Posted by Erland Sommarskog on 10/01/20 11:42

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/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]