[ACCEPTED]-How to test for multiple row actions in a SQL Server trigger?-database
Trigger definitions should always handle 2 multiple rows.
Taken from SQLTeam:
-- BAD Trigger code following:
CREATE TRIGGER trg_Table1
ON Table1
For UPDATE
AS
DECLARE @var1 int, @var2 varchar(50)
SELECT @var1 = Table1_ID, @var2 = Column2
FROM inserted
UPDATE Table2
SET SomeColumn = @var2
WHERE Table1_ID = @var1
The above trigger will only work for the last row in the inserted table.
This is how you 1 should implement it:
CREATE TRIGGER trg_Table1
ON Table1
FOR UPDATE
AS
UPDATE t2
SET SomeColumn = i.SomeColumn
FROM Table2 t2
INNER JOIN inserted i
ON t2.Table1_ID = i.Table1_ID
Yes, if a statement affects more than one 9 row, it should be handled by a single trigger 8 call, as you might want to revert the whole 7 transaction. It is not possible to split 6 it to separate trigger calls logically and 5 I don't think SQL Server provides such a 4 flag. You can make SQL Server call your 3 trigger with multiple rows by issuing an 2 UPDATE or DELETE statement that affects 1 multiple rows.
First it concerns me that you are making 17 the triggers handle multiple rows by using 16 a cursor. Do not do that! Use a set-based 15 statment instead jioining to the inserted 14 or deleted pseudotables. Someone put one 13 of those cursor based triggerson our database 12 before I came to work here. It took over 11 forty minutes to handle a 400,00 record 10 insert (and I often have to do inserts of 9 over 100,000 records to this table for one 8 client). Changing it to a set-based solution 7 changed the time to less than a minute. While 6 all triggers must be capable of handling 5 multiple rows, you must not do so by creating 4 a performance nightmare.
If you can write 3 a select statment for the cusor, you can 2 write an insert, update or delete based 1 on the same select statment which is set-based.
I've always written my triggers to handle 6 multiple rows, it was my understanding that 5 if a single query inserted/updated/deleted 4 multiple rows then only one trigger would 3 fire and as such you would have to use a 2 cursor to move through the records one by 1 one.
One SQL statement always invokes one trigger 6 execution - that's part of the definition 5 of a trigger. (It's also a circumstance 4 that seems to at least once trip up everyone 3 who writes a trigger.) I believe you can 2 discover how many records are being affected 1 by inspecting @@ROWCOUNT.
More Related questions
We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.