Communication betwee two tables

[lucky]

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.

[Erland Sommarskog]

lucky (j.**********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Doug]

homework.