When do triggers fire and when don't they.

Question

Pretty general question regarding triggers in SQL server 2005.

In what situations are table triggers fired and what situations aren't they?

Any code examples to demonstrate would be great.

I'm writing a audit based databases and just want to be aware of any situations that might not fire off the triggers that I have set up for update, delete and insert on my tables.

A example of what I mean,

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

The following statement only fires the update trigger once.

Answer 1

When do you want them to fire?

CREATE TRIGGER AFTER ACTION

That runs after the action (insert update delete) being committed. INSTEAD OF fires the trigger in place of the action.

One of the biggest gotchas with triggers is that they fire whenever an action is performed, even if no rows are affected. This is not a bug, and it's something that can burn you pretty quickly if you aren't careful.

Also, with triggers, you'll be using the inserted and deleted tables. Updated rows are listed in both. This throws a lot of folks off, because they aren't used to thinking about an update as a delete then insert.

The MSDN documentation actually has a pretty in-depth discussion about when triggers fire and what effect they have here.

Answer 2

I thought I'd highlight from the link Eric posted a situation in which a trigger would not fire:

Although a TRUNCATE TABLE statement is in effect a DELETE, it cannot activate a trigger because the operation does not log individual row deletions. However, only those with permissions on a table to execute a TRUNCATE TABLE need be concerned about inadvertently circumventing a DELETE trigger with a TRUNCATE TABLE statement.

Answer 3

On 2008 you can use built in Change Data Capture

Also There are quite a few situations when triggers do not fire, such as:

· A table is dropped.

· A table is truncated.

· Settings for nested and/or recursive triggers prevent a trigger from firing.

· Data is bulk loaded, bypassing triggers.

Answer 4

The following statement only fires the update trigger once.

Any action type statement only fires the trigger once no matter how many rows are affected, triggers must be written to handle multiple row inserts/updates/deletes.

If your trigger depends on only one row at a time being in the inserted or deleted pseudotables, it will fail. And worse it will not fail with an error, it will simply not affect all the rows you want affected by whatever the trigger does. Do not fix this through a loop or a cursor in a trigger, change to set-based logic. A cursor in a trigger can bring your entire app to a screeching halt while a transaction of 500,000 records processes and locks up the table for hours.

Bulk inserts by pass triggers unless you specify to use them. Be aware of this because if you let them by pass the trigger you will need code to make sure whatever happens in the trigger also happens after the bulk insert. Or you need to call the bulk inserts with the FIRE_TRIGGERS option.