Certain Extent

How many triggers should you have per table?

Ideally zero. If you have any then there should be one. There is no guarantee on the ordering of trigger firings, they normally fire based on their age – newly-added triggers fire last. So if you have two triggers that both run on update, you could get into a recursion situation.
If you do have more than one, you have to set the trigger order via a call to sp_settriggerorder to avoid this recursion, or rewrite the trigger.

-- Test trigger recursion

if object_id('TestTriggerRecursion', N'U') > 0
    drop table TestTriggerRecursion

if object_id('tr_TestTriggerRecursionUpdatedDate', N'TR') > 0
    drop trigger dbo.tr_TestTriggerRecursionUpdatedDate

if object_id('tr_TestTriggerRecursionUpdatedBy', N'TR') > 0
    drop trigger dbo.tr_TestTriggerRecursionUpdatedBy
go

create table TestTriggerRecursion
(
    KeyID int primary key clustered
    , Payload varchar(max) NOT NULL
    , LastUpdatedDate datetime NULL
    , LastUpdatedBy sysname NULL
)
go

create trigger dbo.tr_TestTriggerRecursionUpdatedDate on dbo.TestTriggerRecursion
for insert, update
as

print 'tr_TestTriggerRecursionUpdatedDate ran'
update t set LastUpdatedDate = getdate()
from INSERTED i
join TestTriggerRecursion t on t.KeyID = i.KeyID

go

create trigger dbo.tr_TestTriggerRecursionMaintainAuditField on dbo.TestTriggerRecursion
for insert, update
as

print 'tr_TestTriggerRecursionUpdatedBy ran'
update t set LastUpdatedBy = SUSER_NAME()
from INSERTED i
join TestTriggerRecursion t on t.KeyID = i.KeyID

go

-- delete from TestTriggerRecursion
insert into TestTriggerRecursion values (1, 'test1', null, null)
insert into TestTriggerRecursion values (2, 'test2', null, null)
insert into TestTriggerRecursion values (3, 'test3', null, null)

select * from TestTriggerRecursion

-- fails with
/*

(0 row(s) affected)
Msg 217, Level 16, State 1, Procedure tr_TestTriggerRecursionUpdatedBy, Line 6
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

*/

-- exec sp_settriggerorder @triggername = 'tr_TestTriggerRecursionUpdatedBy', @order='First'

How can I get a list of triggers and which tables they apply to?

select so.name as TableName
, st.name as TriggerName
from sys.triggers st
join sys.objects so on so.object_id = st.parent_id

What order do constraints and triggers run?

Constraints, then triggers. In addition it should be noted that the DML operation and the trigger code have separate execution plans, poor code that causes recompiles inside a trigger does not cause outside stored procedures to change. With statement-level compilation/caching in 2005 this is even more true. In addition it should be noted that constraint code is independent of DML operations as well.

Can you say “only fire this trigger if THIS column is updated”?

IF UPDATE(Field1) OR UPDATE(Field2)
BEGIN

END

Do triggers try to run ON UPDATE even if now rows have been affected?

Yes, which is why you should always bail if no rows are affected like so:

IF @@ROWCOUNT = 0
RETURN

As you see below, your entire trigger will fire even on a failed update.

-- Test trigger update behavior with zero rows
if object_id('TestTriggerUpdateBehavior', N'U') > 0
    drop table TestTriggerUpdateBehavior

if object_id('tr_TestTriggerUpdateBehaviorUpdatedDate', N'TR') > 0
    drop trigger dbo.tr_TestTriggerUpdateBehaviorUpdatedDate

create table TestTriggerUpdateBehavior
(
    KeyID int primary key clustered
    , Payload varchar(max) NOT NULL
    , LastUpdatedDate datetime NULL
    , LastUpdatedBy sysname NULL
)
go

create trigger dbo.tr_TestTriggerUpdateBehaviorUpdatedDate on dbo.TestTriggerUpdateBehavior
for update
as

print 'tr_TestTriggerUpdateBehaviorUpdatedDate ran'
update t set LastUpdatedDate = getdate()
from INSERTED i
join TestTriggerUpdateBehavior t on t.KeyID = i.KeyID

go

insert into TestTriggerUpdateBehavior values (1, 'test1', null, null)

update TestTriggerUpdateBehavior set Payload = 'testImpossibleUpdate'
where 1 = 2

select * from TestTriggerUpdateBehavior

So, when should you use triggers?

You should use triggers:

• When you have a clear understanding of how they work
• You have no other option
• You have performance tested your code thoroughly
• You have informed your storage folks, DBA folks, and your mother
• You have prayed about it

In all honesty I’ve only seen a few clean uses for triggers:

1. “audit trigger”: Audit mechanism for straight up insert/delete/update calls. You have tableA, and you want to log all changes to auditTableA - a “copy trigger” does this quite well.
2. “refactor trigger”: A temporary bridge between two phases of a database refactor project. You are migrating data from schema A to schema B, but that last pesty bit of code hasn’t been changed. This release changed 80% of the code and put in a trigger to maintain the data or log to new tables for later testing the remaining 20%. The discipline required to push through the 20% and remove the trigger is rare, so this is sometimes dangerous.
3. “trap trigger”: In a crisis, log where updates are coming from to a specific table. Remove trigger quickly thereafter.
4. “evil trigger”: A trigger created for evil.

Where can I find more information about triggers?

Books online: Triggers.

sqlserver