DDL Triggers in SQL Server

Overview

So far, we have seen how to deal with DML triggers. In this chapter, we will start talking at DDL triggers.

Explanation

What is a DDL Trigger?

Data Definition Language (DDL) triggers are, like DML triggers, stored procedure-like code that fires in response to an event. In this case, it’s fired by a DDL event, which mostly corresponds to a statement like CREATE, ALTER, DROP, GRANT, DENY, REVOKE, UPDATE STATISTICS being executed, but also the execution of certain system stored procedures.

As it happens with DML triggers, DDL triggers can be written in both Transact SQL and CLR code.

DDL Trigger Usage Scenarios

Here is a list of things that you can make using DDL triggers:

• Prevent certain changes to database objects.
• Perform an action, like writing a row in an audit table in response to events in the database, like a table change.
• Record changes on server configuration.
• Keep an audit of who is logging into the server.
• Prevent someone from accidentally dropping a database.

DDL Trigger Syntax

In order to create a DDL trigger, we must use the CREATE TRIGGER statement. As you will see below, it has subtle differences compared to the syntax of DML triggers covered in previous chapters.

CREATE TRIGGER trigger_name   
ON { ALL SERVER | DATABASE }   
[ WITH [ENCRYPTION | EXECUTE AS Clause] ]  
{ FOR | AFTER } { DDL event } 
AS 
{ Your code goes here }  

Additionally, the table describes each of the arguments of the CREATE TRIGGER statement.

DDL Events and the EVENTDATA() Function

By now, we know that DDL triggers are fired in response to DDL events. But, if we want to give some real-world functionality to a DDL trigger, we need a way to get more information about the firing event. Just to make a rough comparison, remember that in the case of DML triggers, we had the deleted and inserted pseudo tables to use as a reference in order to get the picture of the event that fired the trigger. In the case of DDL triggers, we have the EVENTDATA() function that allows us to obtain information about the event that fired the trigger. This function returns an XML string, which we can parse

In the next chapters, I will show you some examples of these types of triggers.

Additional Information

• Extend SQL Server DDL Triggers for more functionality: Part 1 and Extend SQL Server DDL Triggers for more functionality: Part 2
• Grant Execute Permissions to SQL Server Stored Procedures using DDL Triggers