David Tate

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

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

According to the standard developer canon about databases you should avoid triggers like you avoid wearing your “I love goto statements” t-shirt at the company Christmas party (again).  Knowledge of triggers is verboten, almost as bad as saying you love cursors.  Why are they such a bad idea, and why does the DBA let the air out of your Oldsmobile Firenza tires whenever you write one?  Let’s explore these ideas, and leave you being grown up and driving a Oldsmobile for another day. First, a brief overview of triggers if you aren’t familiar with them at all (lucky):

Triggers are blocks of T-SQL that are run upon the firing of certain events within SQL Server, the most common one being an insert, update, or delete to a rows in a table. They can also fire upon login, the creation of tables, and other system events. Triggers have certain constraints that they have to live with such as not being able to return results or perform certain operations such as CREATE DATABASE, and they are fed only certain input – @@ROWCOUNT, the inserted and deleted tables, the ambient transaction. They are typically complained at and about by DBAs and developers as being slow and hard to deal with.

Where do triggers come from?

When a whore and a demon love each other they make a baby, and that baby is a trigger with a cursor in it that handles it own transactions and savepoints whilst trying to send email via a call to the smtp service using xp_cmdshell.

Are triggers in the original relational model?

No, but neither are isolation levels, recovery models, or stored procedures. But never fear, very little that you have heard of is in the original relational model unless you married your sister and eat German chocolate bars in which case you are familiar with joined relations and relvars.

Why are triggers given such a bad name – aren’t they just a certain type of stored procedure that happens at a certain time?

Triggers are given a bad name for a few reasons:

What they are typically used for:

They are quite powerful and it is easy to overuse them for things that are normally better supported by the DBMS in other places. The classic example of this is constraint or foreign key enforcement – a trigger can stop the insert/update from occurring or check the updated data right before the modification. Business logic can live in a trigger, so that it runs “just in time”. Another common pattern is to use a trigger to keep two separate databases in sync – every update to one causes an insert into another across linked server, etc. Triggers are dark-alleyed shortcuts and there are normally better paths to build the functionality you want.

How hard they are to work with:

Most developers don’t know how to properly code a trigger – there I said it.  In addition, they are added T-SQL surface area that doesn’t show up in most tracing mechanisms for laymen (Activity Monitor, syscomments, indirectly in execution plans, a lot of source control systems, etc).

They are slow:

Triggers are inside a transaction doing an insert, update, delete.  This is the essential core of the database system – very close to the DBMS kernel in terms of your application usage. Adding a trigger that takes half a second can bring your system to a messy halt.  In addition, in the past triggers populated the inserted and deleted tables via building a view from reading the transaction log.  This lead many DBAs to see non sequential behavior in their storage mechanisms that were built for sequential writes leading to even greater slowdowns.  As of SQL Server 2005 triggers are implemented via row versioning which thus makes use of the tempdb instead of reading the log, but DBAs never forget.  In either event, the behavior of triggers is simply different than other types of statements, leading DBAs to be even more unhappy – and they are pretty much unhappy in DisneyWorld.

Tune in for Triggers Part 2 Facts and Frequently-Occuring-Problems (FOP)

If you work with more than one database and have the constant need to run the same query across multiple servers, the undocumented stored procedure sp_MSforeachdb can be useful; see below example comparing stored procedures across servers using a simple checksum to tell if they are the same exact contents. sp_msforeachtable is another oldie but goodie that can be used for do an action per table or examine table properties easily – such as examining table properties that are not exposed from sys.tables.If you are using SQL Server 2008 there is a more fully supported way to do this and many other nice admin features: Multiple Server Query Execution in SQL Server 2008. Example of sp_MSforeachdb to test if stored procedure contents are exactly the same across databases:

1
2
3
4
5
6

DECLARE @SQL VARCHAR(MAX), @procname sysnameset
@procname = 'cspExampleList'
SET @SQL = ' use ? select db_name(), sp.name, checksum(sc.text) from sys.procedures sp join syscomments sc on sc.id = sp.object_id where name = 'SET @SQL = @SQL + '''' + @procname + ''''-- select @sqlcreate table #Results (dbname varchar(max), procname varchar(max), chksum int)
INSERT INTO #Resultsexec sp_MSforeachdb @SQL
SELECT * FROM #Results
DROP TABLE #Results