bob bob - 17 days ago 8
SQL Question

Writing trigger in SQL Server

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.