Msg 102, Level 15, State 1, Procedure Mon_Trigger, Line 24

Question

Post reply

Msg 102, Level 15, State 1, Procedure Mon_Trigger, Line 24

marwenG

Say Hey Kid

Points: 703
More actions
August 10, 2009 at 9:46 am

#209437

I have 2 tables:
-Personnel : IDENT primary key
-Participants:IDENTPART primary key IDENT Foreign Key
i want to create a trigger that don't allows me to A PERSONNEL to HAVE MORE THAN 2 PARTICIPANTS
I create a trigger:
CREATE TRIGGER Mon_Trigger
ON Participants
AFTER INSERT,UPDATE
AS
declare @nbre_Part int ,@nbre_Parti int
BEGIN
select @nbre_Part=count(*)
from Inserted
select @nbre_Part=count(*)
from updated
if ( @nbre_Part > 2 )
Begin
ROLLBACK
RAISERROR [Nombre Limite dépassé!!!!!!]
END
if ( @nbre_Parti > 2 )
Begin
ROLLBACK
RAISERROR [Nombre Limite dépassé!!!!!!]
END
END
GO
when i excute,this message appears:
Msg 102, Level 15, State 1, Procedure Mon_Trigger, Line 19
Syntaxe incorrecte vers 'Nombre Limite dépassé!!!!!!'.
please help me
thks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

matt6288 SSCertifiable Points: 5058 More actions · Answer 1

I think you need to change the syntax of the raiserror line.

Here it is from BOL:

RAISERROR ( { msg_id | msg_str | @local_variable }

{ ,severity ,state }

[ ,argument [ ,...n ] ] )

[ WITH option [ ,...n ] ]

--EXAMPLE

RAISERROR (N'This is message %s %d.', -- Message text.

10, -- Severity,

1, -- State,

N'number', -- First argument.

5); -- Second argument.

-- The message text returned is: This is message number 5.

GO

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 2

marwenG (8/10/2009)
select @nbre_Part=count(*)
from Inserted
select @nbre_Part=count(*)
from updated
i

There's no such thing as an 'updated' table. The two pseudo-tables visible in triggers are inserted and deleted.

Inserted contains new rows, when the operation is an insert, and the new values, when the operation is an update. Deleted contains the old values, when the operation is an update, and the rows to be deleted when the operation is a delete.

Also to note is that a trigger fires once per statement and the inserted and deleted tables contain all rows affected. The way your trigger is written it'll throw the error any time more than 2 rows are inserted, updated or deleted in the Participants table, regardless of the value of the columns.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass