bob bob - 14 days ago 5
SQL Question

writting trigger in sql

i am trying to write a trigger checking the value in column table comparing it
to a count of value from another column this work perfectly but my trigger
itself written with the same idea doesn't prompt when i expected it to do so
this is my test it works fine

declare @nombreMax int
declare @cours int
declare @maxtu int
set @cours=1
set @maxtu=(select max_etudiant from Cours where id=@cours)
select @nombreMax=count(*) from [dbo].[ETUDIANT] where ETU_COURS=@cours
if (@nombreMax>@maxtu) print 'depassé de '
else print 'ok'


but this doesn't work what am i missing?

CREATE TRIGGER [dbo].[VerifNombreEtu]
ON [dbo].[ETUDIANT]
AFTER INSERT,update
AS
BEGIN
DECLARE @ETU_ID INT
DECLARE @cours nvarchar(50)
DECLARE @status INT
DECLARE @nombreEtu INT
DECLARE @Max INT

DECLARE Maxc2 cursor fast_forward for
select inserted.ETU_COURS from Inserted

open Maxc2
fetch next fROm Maxc2 into @ETU_ID
set @status=@@FETCH_STATUS
while @status=0
begin

set @Max=(select max_etudiant from Cours where id=@cours)
select @nombreEtu=count(*) from [dbo].[ETUDIANT] where ETU_COURS=@cours
if(@nombreEtu>@Max)
begin
raiserror('max students reached',17,1)
rollback;
end
fetch next FROM Maxc2 into @ETU_ID
set @status=@@FETCH_STATUS
END
close Maxc2;
deallocate Maxc2;

END

Answer

The cause could be, that the variable @cours is not assigned a value in the trigger.