1. DDL triggers
So far, we've only talked about DML triggers.
But as mentioned earlier, triggers also come in Data Definition Language (DDL) flavors.
In this lesson, you are going to learn how to create DDL triggers and how they are different from DML triggers.
2. Definition and properties
The simplest way to illustrate the specific properties of DDL triggers is to compare them with DML triggers.
The biggest difference is indicated by the names of the trigger types.
_DML triggers_ respond to events associated with DML statements: INSERT, UPDATE, and DELETE.
On the other hand, DDL triggers respond to events associated with DDL statements like CREATE, ALTER, and DROP.
While DML triggers can be used with both the AFTER and INSTEAD OF keywords, the DDL triggers accept only the AFTER keyword.
DML triggers are attached to tables or views, while DDL triggers are created at the database or server level.
Furthermore, we can make use of the "inserted" and "deleted" special tables when working with DML triggers, but there are no special tables for use with DDL triggers.
That said, you can use your own defined tables to keep track of changes when using DDL triggers.
3. AFTER and FOR
When dealing with DDL triggers, you'll often see syntax like this.
You might expect the AFTER keyword to come before CREATE_TABLE, but the FOR keyword is used instead.
4. AFTER and FOR
That's because for SQL Server triggers, the `FOR` and `AFTER` keywords have the same result.
To minimize the potential for confusion, people often use the FOR keyword for DDL triggers and the AFTER keyword for DML triggers.
We'll stick to that convention here, but be aware that both versions are correct and will behave in the same way.
5. DDL trigger prerequisites
Let's do a quick analysis of an example DDL trigger.
We'll create a trigger that will log information about table changes in a database.
The trigger will be created at the database level.
The changes will be written to a dedicated log table and will include details about the creation, alteration, and deletion of tables.
The trigger name will be "TrackTableChanges".
6. DDL trigger definition
Creating a DDL trigger is not much different from creating a DML trigger.
We start with the CREATE TRIGGER syntax, only this time we attach the trigger at the database level.
As mentioned, we use the FOR keyword for DDL triggers. After the keyword, we include the DDL statements that will fire the trigger.
Note that although the DDL statements included in the trigger definition resemble the actual DDL syntax, an underscore is used between the words instead of a space.
The statement that is going to perform the data insert comes next.
This statement will log the information from the EVENTDATA function, as well as details about the user performing the change.
The EVENTDATA function actually holds information about the event that runs and fires the trigger.
Pretty cool, right?
7. Preventing the triggering events for DML triggers
The `INSTEAD OF` statement cannot be used to prevent the triggering action from happening for DDL triggers.
So are DDL triggers used only as AFTER triggers?
The answer is no. You can define a trigger to roll back the statements that fired it.
In this example, we don't want the users to remove tables from the database, so we set DROP_TABLE as the event firing the trigger.
When the trigger fires we throw an error but also roll back the initial operation, so the table deletion does not take place.
We now have a database trigger that prevents the firing event from happening. It's that simple.
8. Let's practice!
How about we put those examples into practice?