Trigger event is fired only once for Microsoft SQL Server 2005 DB if more than one rows updated?

Question

I have a table MyTable with a trigger defined like this:

ALTER TRIGGER [MyTableInsertDeleteUpdate] 
  ON  [dbo].[MyTable]
 AFTER  INSERT,DELETE,UPDATE
AS 
DECLARE @id int;
BEGIN
   SELECT @id = ins.id FROM inserted ins;
   IF (@id IS NOT NULL)
   BEGIN
      -- insert a new record to audit table
      PRINT 'inserted/updated id: ' + CAST(@id AS VARCHAR);
   END
END

I realize that if more than one rows are updated like this,

UPDATE MyTable SET name = 'test rows' WHERE id in (1, 2, 3);

the tigger is called only once and only the firstone in [inserted] is updated. Actually, [inserted] may have more than one rows (3 in this case if id 1, 2,3 exist). In order words, the trigger is not fired on each row. Is that right?

I am using Microsoft SQL Server 2005.

Answer 1

Yeah the trigger is fired once per statement (not once per row) that makes the changes you are subscripting to. It will even fire if no rows where affected.

http://msdn.microsoft.com/en-us/library/ms189799(SQL.90).aspx

Answer 2

As Hojou said, your trigger will fire once per statement rather than once per affected row. This is different to databases like Interbase and Firebird, and threw me when I first started using SQL Server.

The whole point of the inserted and deleted 'virtual' tables is because the events are record-SET based, not row-based.

There are any number of tutorials out there that cover writing sql to process the inserted/deleted tables, but watch out for the shovelware ones. I've seen more than a couple of so-called tutorials that have just been copy/pasted from another database platform and won't actually work in SQL Server as they claim to (one of the top hits for 'SQL Server trigger example' in Google gets it completely wrong for UPDATE statements).

This is a reasonable introduction to Triggers and the concepts required to make sense of the inserted and deleted tables, with an explanation of why you will be missing events in your own example. The Microsoft docs themselves are reasonably useful once you get past their dull, lifeless structure and writing-style.

Answer 3

To insert records to an audit table from an insert you would do something like this in the trigger:

insert auditable (field1, field2, insert_date, insertedBy)
select field1, field2, getdate(), user_Name() from inserted

No fooling around with setting variables just a a plain insert based on a select statment.

Personally I would have a separate trigger for inserts, updates and deletes as you want differnt code for each.