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.

Solution 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.

Solution 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.

Solution 3

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.

Solution 4

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.

View more solutions

Comments

[Trojan.ZBOT]

Can you give an example which shows how a trigger can create problems when no rows are affected ? Thanks.

[Eric]

Take a look at this one: sqlfiddle.com/#!6/be3d3/3 . I've seen triggers scheduled to fire off emails to folks. If you have a process that idly inserts or updates records that rarely affects a row but just polls it, that can burn you pretty quickly. Always check if rows were affected in your triggers, as a rule of thumb!

[gmaness]

I know this is old, but just gave an up vote for explaining the update using both inserted and deleted. This saved my bacon after fighting for a couple of hours with my trigger design.

[Frank Bryce]

Does a trigger created by 'CREATE TRIGGER AFTER INSERT ...' fire when 'UPDATE' is performed? You mention UPDATE is a delete then insert so I figured it was but there is no explicit mention of that.

[Graeme]

@JohnCarpenter - no--the action is in line with the SQL verb that was issued. What he meant was whenever we say "UPDATE", sql server keeps a "before" copy (deleted table) and an "after" copy (inserted table) that matches the schema of the table you updated. These tables (inserted, deleted) are only available inside the UPDATE (and hence, inside the TRIGGER itself). Each trigger will only fire when you tell it to (e.g., INSERT, UPDATE or DELETE).

[Graeme]

@JohnCarpenter - I should also mention: the INSERT action includes the inserted table (aka, pseudotable) and the DELETE action includes the deleted table. Only UPDATE has both (since there is always a 'before' and 'after' during an UPDATE).

[kuklei]

I have found out that if you have two triggers on one table one that fires on insert and another that fires on update, you could be in a situation when both fire on insert. My scenario was that I wanted to keep a document version on every modification. I had the on insert trigger to set the version as 1, hence performing a modification of the record and this also fired the update trigger that was setting the version as 2 since the insert trigger updated the just inserted record. Just beware of this situation when the insert trigger modifies something causing the update trigger to also fire.

[Andy]

The triggers don't run after the action is committed; they run inside the same transaction immediately after the action has taken place, so if there is a rollback it will rollback any updates done by the trigger.