SQLskills SQL101: Using DDL Triggers

Over the past couple of months, SQLskills has embarked on a new initiative to blog about basic topics, which we’re calling SQL101. We’ll all be blogging about things that we often see done incorrectly, technologies used the wrong way, or where there are many misunderstandings that lead to serious problems. If you want to find all of our SQLskills SQL101 blog posts, check out SQLskills.com/help/SQL101.

DDL Triggers

One very useful safety feature that was added to the product with the release of SQL Server 2005 is Data Definition Language (DDL) triggers. Even though they have been available for quite some time, I still don’t see that many people actually using them on their systems, which I think is a shame.

DDL triggers are described by the online documentation like this:

DDL triggers fire in response to a variety of Data Definition Language (DDL) events. These events primarily correspond to Transact-SQL statements that start with the keywords CREATE, ALTER, DROP, GRANT, DENY, REVOKE or UPDATE STATISTICS.

Basically, when a T-SQL command does something that affects the metadata or schema of your database, you can capture and log some useful information about what was changed, what the change was, when it was changed, and who did it. Depending on how you configure the DDL trigger, you can capture things that actually are metadata changes (such as CREATE, ALTER or DROP) for things like tables, views, stored procedures, functions, and indexes. You can also capture things that I don’t really consider true metadata changes, such as an ALTER INDEX REORGANIZE command.

DDL Trigger Actions

Just like with a DML Trigger, you have to decide what happens when a DDL Trigger fires. For example, one action that Microsoft likes to use in their documentation is to simply have a ROLLBACK command, along with an error message that indicates what happened. This is designed to prevent someone (perhaps you) from accidentally making a terrible mistake such as dropping a table from a Production database.

DDL Trigger Usage

Another common usage is to simply log relevant information about all DDL changes that you decide to capture to a table that you create in each database. This can be very useful when multiple people have admin rights in your Production databases. Even if that is not the case, having a record of all DDL changes to a database can be very helpful.

Another use for DDL Triggers is to capture what is happening with your index maintenance. DDL commands such as ALTER INDEX REORGANIZE and ALTER INDEX REBUILD can be logged to help you analyze your index maintenance. For example, if you see the same index being reorganized or rebuilt on a frequent, regular basis, you might want to consider lowering the fill factor on that index to reduce how quickly it becomes fragmented, which will decrease how often it needs to be reorganized or rebuilt.

Conclusion

DDL Triggers can be very useful, and they are very easy to use for a number of different purposes. They are not as secure or as powerful as SQL Server Audit, but they are available in all editions of SQL Server, starting with SQL Server 2005. They are also much easier to set-up and use. I have an example of how to create a table to log some DDL changes, along with an actual DDL trigger to capture the changes, available here.