This is part of a 3 part epic mini-series on triggers, view Part 1 and Part 3.
When are triggers fired, and how many times?
For your standard missionary position trigger, they are fired once per batch. So if you have an update statement that affects 57 rows, then the DML AFTER trigger fires *once* but the deleted and inserted magic tables have 57 rows in them. One common anti-pattern is not handling multiple values in the inserted/deleted tables. Code to prove the firing behavior of triggers:
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 47 48 49 50 51 52 53 54 55 56 57 58 59 60 | -- Test trigger firing cardinality IF OBJECT_ID('TestTriggerFiring', N'U') > 0 DROP TABLE TestTriggerFiring IF OBJECT_ID('TestTriggerAudit', N'U') > 0 DROP TABLE TestTriggerAudit IF OBJECT_ID('tr_TestTriggerFiring', N'TR') > 0 DROP TRIGGER dbo.tr_TestTriggerFiring CREATE TABLE TestTriggerFiring ( KeyID INT PRIMARY KEY CLUSTERED, Payload VARCHAR(MAX) NOT NULL, LastUpdatedDate DATETIME NULL, LastUpdatedBy sysname NULL ) CREATE TABLE TestTriggerAudit ( TriggerName SYSNAME, DateFired DATETIME NOT NULL, ActionType VARCHAR(10) NOT NULL, KeyID INT NOT NULL, Stamp UNIQUEIDENTIFIER ) GO CREATE TRIGGER dbo.tr_TestTriggerFiring ON dbo.TestTriggerFiring FOR INSERT, UPDATE AS DECLARE @MyGuid uniqueidentifierset @MyGuid = NEWID() INSERT INTO TestTriggerAudit (TriggerName, DateFired, ActionType, KeyID, Stamp) SELECT 'tr_TestTriggerFiring', GETDATE(), 'inserted', i.KeyID, @MyGuid FROM INSERTED i GO /* DELETE FROM TestTriggerFiring DELETE FROM TestTriggerAudit */ INSERT INTO TestTriggerFiring SELECT TOP 3 object_id, name, NULL, NULL FROM sys.objects GO INSERT INTO TestTriggerFiring VALUES (-1, 'test4', NULL, NULL) WAITFOR DELAY '00:00:001' GO INSERT INTO TestTriggerFiring VALUES (-2, 'test5', NULL, NULL) WAITFOR DELAY '00:00:001' GO INSERT INTO TestTriggerFiring VALUES (-3, 'test6', NULL, NULL) WAITFOR DELAY '00:00:001' GO SELECT * FROM TestTriggerAudit |
What are the inserted/deleted magic tables? How do they work?
The inserted and deleted tables hold the date being changed. Depending on what you are doing they hold different data:
Insert
Inserted – holds the new data
Deleted – is empty
Update
Inserted – holds new data
Deleted – holds old data
Delete
Deleted – data being deleted
Inserted – is empty
The inserted and deleted tables are not indexed, so take care in querying them in the wrong way. Given the logic above, if possible it is normally cleaner to not combine an insert/update trigger. If you have to, the below if my template for doing so:
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 | IF OBJECT_ID('tr_Example_Update', 'TR') > 0 DROP TRIGGER dbo.tr_Example_Update GO CREATE TRIGGER dbo.tr_Example_Update ON dbo.Test FOR UPDATE, INSERT AS IF @@ROWCOUNT = 0 RETURN SET NOCOUNT ON IF UPDATE(Field1) OR UPDATE(Field2) BEGIN -- Update IF (SELECT COUNT(1) FROM DELETED) > 0 BEGIN PRINT 'Update logic' END ELSE BEGIN PRINT 'Insert logic' END END SET NOCOUNT OFF GO |
Are triggers fired on bulk insert?
No by default, but you can turn them on: Controlling Trigger Execution When Bulk Importing Data
Tune in for Triggers Part 3: FAQ and FOP continued