Sign in Subscribe
sql

Triggers Part 2: Facts and Frequently-Occuring-Problems (FOP)

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:

-- 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:

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

Read next