Triggers & Multiple Inserts

[tvdbon]

Hi,

I've created the following trigger to keep log of inserts on our recipe table :


CREATE TRIGGER tr_recipe_insert
ON RecipeTable
FOR INSERT
AS

IF EXISTS (SELECT * FROM inserted)
BEGIN
DECLARE @Value varchar(20)
SET @Value = (SELECT REC_PartNo from inserted)
INSERT INTO OperationsLog values( GETDATE(), 'RecipeTable', 'Insert', @Value )
END

, but it doesn't seem to work for multiple inserts. What am I doing wrong ?

 

[SamGarland]

The problem is that you are using a variable that can only hold a single value. SET @Value = (SELECT REC_PartNo from inserted) will set @Value to the last REC_PartNo retrieved by the SELECT statement. Try something like this:

CREATE TRIGGER tr_recipe_insert
ON RecipeTable
FOR INSERT
AS

IF EXISTS (SELECT * FROM inserted)
BEGIN
INSERT INTO OperationsLog
SELECT GETDATE(), 'RecipeTable', 'Insert', REC_PartNo from inserted
END

 

[JamesLean]

Just a small point, there's no need for the existence check on the inserted table - this is an INSERT trigger so there will always be something in the inserted table!

--James

 

[SamGarland]

Right you are. I just thought of that myself.

 

[SamGarland]

Just a bit of a clarification (I've been away from a computer for a few days and this has been bugging me).

In the case above the IF EXISTS check is probably not neccesary, however the insert trigger fires once for every insert statement that is executed and it is possible that the inserted pseudotable can be empty.

An insert statement in the form below can cause this to happen when the select statement returns an empty resultset:

insert into table_x select ...

In AFTER triggers, it is possible to efficiently determine how many rows were affected by testing @@ROWCOUNT in the trigger.