Transcript Presentation - SQLPerformance.com

5 Ways to Write DML Triggers That Don’t Suck
Aaron Bertrand
SQL Sentry
[email protected]
About Me
Aaron Bertrand
Senior Consultant
@AaronBertrand
http://sqlsentry.com/
Microsoft MVP since 1997
Author, MVP Deep Dives 1 & 2
http://sqlblog.com/
http://sqlperformance.com/
[email protected]
2
© SQLintersection. All rights reserved.
http://www.SQLintersection.com
Agenda











What are triggers?
Why do we use triggers?
How do triggers work?
Triggers can affect more than one row
Triggers need to be as quick as possible
Avoid UPDATE() and COLUMNS_UPDATED()
MERGE has funny effects on triggers
INSTEAD OF triggers
Measure trigger performance
A few other tips
Other alternatives
3
© SQLintersection. All rights reserved.
http://www.SQLintersection.com
What are triggers?
 We’ll talk about DML triggers today

There are also DDL triggers and logon triggers
 Piece of code that runs in response to some DML action

INSERT, UPDATE, DELETE, MERGE
4
© SQLintersection. All rights reserved.
http://www.SQLintersection.com
Why do we use triggers?
 Variety of purposes

Enforcing business logic


Facilitating foreign keys



Tracking changes, logging activity, sending e-mail, updating LastModified
Maintaining peripheral data


FKs can’t span databases or servers
FKs can cycle or have multiple paths, eliminating CASCADE options
Auditing


Rolling back violations not easily caught by constraints
Updating rank column, adjusting aggregates
INSTEAD OF triggers on views
5
© SQLintersection. All rights reserved.
http://www.SQLintersection.com
How do triggers work?
 inserted & deleted pseudo-tables contain *all* affected rows



inserted contains new version of row
deleted contains old version of row
Both are populated on update
 Pseudo-tables use version store


Even if you don’t enable snapshot
This means triggers can contribute to tempdb contention
 Much improved mechanism

Used to read the transaction log backwards to reconstruct affected rows
6
© SQLintersection. All rights reserved.
http://www.SQLintersection.com
Triggers aren’t single row
 SQL Server fires triggers *per action* not *per row*

Don’t store “the row” in variables

Don’t “fix” that by implementing a loop or cursor

You are always operating on a set – treat this like one, too
7
© SQLintersection. All rights reserved.
http://www.SQLintersection.com
Get In, Get Out
 Triggers – when necessary – need to be quick

Avoid any reliance on external resources




Sending mail, writing to log files, xp_cmdshell, CLR
Use Service Broker or optimized queue tables to perform additional work
Database Mail is ok, but I still prefer a queue table – more control
Optimize code paths




Test the plans for the operations in your trigger
Avoid involving tables with high contention / concurrency
Use short circuit operations like EXISTS when possible
And again, avoid cursors or loops
8
© SQLintersection. All rights reserved.
http://www.SQLintersection.com
Avoid UPDATE() / COLUMNS_UPDATED()
 Not very useful to know when values have actually changed

Or which specific rows had values that actually changed
 Useful only to know what columns are referenced in the UPDATE
9
© SQLintersection. All rights reserved.
http://www.SQLintersection.com
Test trigger logic with MERGE
 Trigger fires once per DML action, not once per MERGE statement

Things like @@ROWCOUNT are unreliable
 Please MERGE with caution anyway:
http://bit.ly/merge-with-caution
10
© SQLintersection. All rights reserved.
http://www.SQLintersection.com
Use INSTEAD OF Triggers
 Can be more efficient to prevent than to do and then undo

Particularly if it is a log-heavy operation
 No free lunch, though


You have to re-write the DML statement to pull from inserted/deleted
Worktable instead of version store
11
© SQLintersection. All rights reserved.
http://www.SQLintersection.com
Measure trigger performance
 sys.dm_exec_trigger_stats

Get max / min / avg / last reads, writes and durations for every trigger
SELECT
[trigger] = tr.name,
tr.is_instead_of_trigger,
[table] = s.name + '.' + o.name,
last_execution_time, execution_count,
max_elapsed_time, max_logical_reads,
avg_elapsed_time = ts.total_elapsed_time*1.0/execution_count
FROM sys.dm_exec_trigger_stats AS ts
INNER JOIN sys.triggers AS tr ON ts.[object_id] = tr.[object_id]
INNER JOIN sys.objects AS o ON tr.parent_id = o.[object_id]
INNER JOIN sys.schemas AS s ON o.[schema_id] = s.[schema_id]
WHERE ts.database_id = DB_ID()
AND ts.[type] = N'TR' AND tr.is_disabled = 0;

However:

Not persisted through actions like service restarts
12
© SQLintersection. All rights reserved.
http://www.SQLintersection.com
A few other tips

Be very careful about nested triggers


Always use SCOPE_IDENTITY() or OUTPUT, not @@IDENTITY


If you do need multiple triggers, use sp_settriggerorder
If you perform multiple operations, put ones most likely to fail first


(Trigger could write to a table that has its own IDENTITY column)
Check if a trigger exists on a table before you add another one


(Trigger writes to table that has its own triggers)
If work may rollback, don’t waste effort
Use source control, since triggers are less discoverable
13
© SQLintersection. All rights reserved.
http://www.SQLintersection.com
Don’t write triggers at all
 Don't use triggers when you don't need them

For example: INSERT trigger that updates CreatedDate – use a default!
 Alternatives:


Default / check constraints
Computed columns




Choose when to take the hit: PERSISTED
Indexed views
Change Data Capture / Change Tracking / Auditing
Perform logic in app/procedures

(Restrict all data access to stored procedures)
14
© SQLintersection. All rights reserved.
http://www.SQLintersection.com
THANK YOU!
15
© SQLintersection. All rights reserved.
http://www.SQLintersection.com