Triggers Part 3: FAQ and FOP Continued

This is part of a 3 part epic mini-series on triggers, view Part 1 and Part 2.

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 6Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).*/

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

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:

  • 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.
  • 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.
  • trap trigger: In a crisis, log where updates are coming from to a specific table.  Remove trigger quickly thereafter.
  • evil trigger: A trigger created for evil.

Where can I find more information about triggers?

Books online: Triggers