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

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.