Communication betwee two tables

Hai,

I am having a query that i want to communicate two tables that
one is school table & other is class table as shown below

school table:
Class strength
LKG 30
UKG 40

class table:(LKG)

Rollno name
1 rohit
2 baba

Now my query is that as i defined strength of the LKG class as 30, if
i insert 31st in to LKG i need to get error, that not to enter. That
implies that when i am going to enter 31 student in LKG table it should
not accept asthe data corresponding to the school table.Is it possible,
if it say the coding, as i am new to SQL plz help me in coding.

thanks,
lucky.lucky (j.v.s.s.baba@.gmail.com) writes:
> I am having a query that i want to communicate two tables that
> one is school table & other is class table as shown below
>
> school table:
> Class strength
> LKG 30
> UKG 40
>
> class table:(LKG)
>
> Rollno name
> 1 rohit
> 2 baba
> Now my query is that as i defined strength of the LKG class as 30, if
> i insert 31st in to LKG i need to get error, that not to enter. That
> implies that when i am going to enter 31 student in LKG table it should
> not accept asthe data corresponding to the school table.Is it possible,
> if it say the coding, as i am new to SQL plz help me in coding.

Wait here, you are saying that you have one table LKG, one table UKG etc?
That is not a correct design. Rather you should have a table classes
with a primary key (classcode, rollno).

Once there, you can have a trigger on the classes table:

CREATE TRIGGER class_tri ON classes FOR INSERT, UPDATE AS
IF EXISTS (SELECT *
FROM school s
JOIN (SELECT c.classcode, cnt = COUNT(*)
FROM classes c
WHERE EXISTS (SELECT *
FROM inserted i
WHERE i.classcode = c.classcode)) AS c
ON s.class = c.classcode
WHERE c.cnt > s.strength)
BEGIN
ROLLBACK TRANSACTION
RAISERROR('One or more classes are over-booked.', 16, 1)
RETURN
END

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||homework.