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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46
-- 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29
-- 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?