trigger

[postmannie]

Struggling with this one,

Got two tables

Student (national_insurance_nos,name,date_of_birth,room_nos)

ClassRoom (room_nos,builing_name,number_of_seats)

Trigger required for a constraint - number of students cannot be greater than the number of seats in a class room

thanks in advance

 

[rac2]

From Transact SQL Programming by Kline, Gould, and Zanevsky published by O'Reilly, page 495. This is for MS SQL Server.

CREATE TRIGGER ClassRoomSeats
ON Student
FOR insert
AS
DECLARE @max_seats INT
DECLARE @currently_enrolled INT

SELECT @max_seats = number_of_seats
  FROM ClassRoom
  WHERE inserted.room_nos = ClassRoom.room_nos

SELECT @currently_enrolled = COUNT(*)
  FROM Student
  WHERE Student.room_nos = inserted.room_nos

IF @currently_enrolled + 1 > @max_seats

BEGIN
      ROLLBACK TRIGGER WITH RAISERROR 50002
      "This classroom is full"
END

Please let me know if this helps. I just worked it up from the example in the book.

 

[postmannie]

rac2

thanks for the reply, unfortunately it does not work. Using sybase if that helps.

thanks in advance