5 Ways to Write DML Triggers That Don’t Suck

1. 5 Ways to Write DML Triggers That Don’t Suck Aaron Bertrand SQL Sentry abertrand@sqlsentry.com

2. About Me Aaron BertrandSenior Consultant@AaronBertrand Microsoft MVP since 1997 Author, MVP Deep Dives 1 & 2 http://sqlblog.com/ http://sqlperformance.com/ abertrand@sqlsentry.com http://sqlsentry.com/

3. 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

4. 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

5. Why do we use triggers? • Variety of purposes • Enforcing business logic • Rolling back violations not easily caught by constraints • Facilitating foreign keys • FKs can’t span databases or servers • FKs can cycle or have multiple paths, eliminating CASCADE options • Auditing • Tracking changes, logging activity, sending e-mail, updating LastModified • Maintaining peripheral data • Updating rank column, adjusting aggregates • INSTEAD OF triggers on views

6. 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 tempdbcontention • Much improved mechanism • Used to read the transaction log backwards to reconstruct affected rows

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

8. 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

9. 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

10. 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

11. 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

12. Measure trigger performance • sys.dm_exec_trigger_stats • Get max / min / avg / last reads, writes and durations for every trigger • However: • Not persisted through actions like service restarts 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;

13. A few other tips • Be very careful about nested triggers • (Trigger writes to table that has its own triggers) • Always use SCOPE_IDENTITY() or OUTPUT, not @@IDENTITY • (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 • If you do need multiple triggers, use sp_settriggerorder • If you perform multiple operations, put ones most likely to fail first • If work may rollback, don’t waste effort • Use source control, since triggers are less discoverable

14. 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)

15. THANK YOU!