By: Daniel Farina
Overview
So far, we have seen how to deal with DML triggers. In this chapter we will start talking at DDL triggers.
What is a DDL Trigger?
Data Definition Language (DDL) triggers are, like DML triggers, stored procedure fashioned 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.
| Argument | Description |
|---|---|
| ALL SERVER | DATABASE | This will set the scope of the trigger. ALL SERVER: The scope is the entire SQL Server instance. DATABASE: Sets the scope of the trigger to the specific database on which the trigger is being created. |
| ENCRYPTION | Encrypts the trigger definition in the metadata. |
| EXECUTE AS | Allows to change the security context on which the trigger runs. For example you can set the trigger to be executed with db_owner rights even if the event that fired the trigger has less permission. |
| FOR | AFTER | You can use either of these options. |
| DDL event | The name of a Transact-SQL language event that, after launch, causes a DDL trigger to fire. You can see the full list of events available on this page: https://docs.microsoft.com/en-us/sql/relational-databases/triggers/ddl-events |
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
