Multi-row comparison in triggers

[mrt78]

I have a trigger which was developed for single row updates, but it needs to handle multi-row updates to support 2005. The basis of the trigger is that it does a value comparison on a specific field to see if it changed and then reacts based on the change.

Example:

CREATE TRIGGER check_for_change
ON mytable FOR INSERT, UPDATE, DELETE
AS
DECLARE
@old_key VARCHAR(20),
@new_key VARCHAR(20)
BEGIN
SELECT
@old_key = del.key
FROM deleted del

SELECT
@new_key = ins.key
FROM inserted ins


IF( NOT(@old_key = @new_key)
BEGIN
Do something ...
END
END

Can anyone give me some pointers on how to accomplish this? Also it would be great if I could also get a recommendation for a book which would help me with the multi-row update concept in the future.

Any help would be appreciated.


Thanks!

 

[ESquared]

rewrite your BEGIN - DO something ... - END section to perform its actions set-based instead of individually, then it's easy:

UPDATE Q
SET Q.Blah = Gorp
FROM
   Q
   INNER JOIN Inserted I ON Q.ID = I.ID
   INNER JOIN Deleted D ON Q.ID = D.ID
WHERE
   Coalesce(I.blink, '') COLLATE Latin1_General_BIN <> Coalesce(D.blink, '') COLLATE Latin1_General_BIN

If you absolutely can't rewrite it to be set-based, then you're stuck with using a cursor or similar construct to do your thing to each row:

declare @i int
select id = identity(int, 1, 1), * into #temp from inserted
set @i = 1
declare @val1 varchar(10), @val2 datetime
while @i is not null begin
   select val1 = @val1, val2 = @val2 from #temp where id = @i
   exec myimpossibleset-basedproc @val1, @val2
   select @i = min(id) from #temp where id > @i
end

 

[SQLSister]

No trigger no matter what the version of SQL server you are using should ever be written for single record imports. This will sooner or later create a problem.

Basically what you do is is use an insert, update or delte statement that uses joins to the inserted or deleted (or both depending on the task) pseudotable. Eric has shown you the syntax for an update, look in BOL for how to use joins in the insert and delete statments if you need them as well when re-writing your trigger.

Do not use a loop or cursor though becasue it seems simpler to fix. Performance will suffer tremendously in multi-row actions. I recently replaced a cursor in a trigger and changed the time to process 40000 records from 42 minutes to 32 seconds in a test. Now this being a cursor in a trigger (and thus unavoidable), the 40000 record insert would have been blocking any other users from that table in the meantime. Do you really want to introduce this kind of thing to your database?

You may think now that this table will never have multi-row actions because the user interface only acts on one row at a time. However, the user interface is often not the only way data gets into, is deleted from or is updated inthe database. At some point someone will ask to have a big group of reciords inserted becasue it would take too long to manually enter them from this spreadsheet with 10000 records or someone will want all the prices raised by 10% or will want to delete all the records belonging to a former client. Even if these tasks are done from Query Analyzer or management studio, they still will invoke the triggers on the table.


"NOTHING is more important in a database than integrity." ESquared