A SQL Server DBA myth a day: (4/30) DDL triggers are INSTEAD OF triggers

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.)

Myth #4: DDL triggers (introduced in SQL Server 2005) are INSTEAD OF triggers.

FALSE

DDL triggers are implemented as AFTER triggers, which means the operation occurs and is then caught in the trigger (and optionally rolled-back, if you put a ROLLBACK statement in the trigger body).

This means they’re not quite as lightweight as you might think. Imagine doing the following:

ALTER TABLE MyBigTable ADD MyNewNonNullColumn VARCHAR (20) DEFAULT 'Paul';

If there’s a DDL trigger defined for ALTER_TABLE events, or maybe even something more restrictive like DDL_TABLE_EVENTS, every row in the table will be expanded to include the new column (as it has a non-null default), and then the trigger will fire and the operation is rolled back by your trigger body. Not ideal at all. (Try it yourself and look in the log with fn_dblog – you’ll see the operation rollback.)

What would be better in this case is to specifically GRANT or DENY the ALTER permission, or do something like only permitting DDL operations through stored-procedures that you create.

However, DDL triggers do allow you to effectively stop it happening, but in a relatively expensive way. And they do allow you to perform auditing of who did what, so I’m not saying they’re without use – just be careful.

Kimberly has a great post on DDL triggers at “EXECUTE AS” and an important update your DDL Triggers (for auditing or prevention).

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.