1. Use cases for DDL triggers
Now it's time to look at some use cases for DDL triggers.
This lesson will only give you an overview of DDL triggers at the database and server level.
The topic is more complex than what you will be learning here, but this lesson will provide you with the fundamentals to get you started.
2. DDL trigger capabilities
DDL triggers can be created at the database level or at the server level.
Where the trigger is created will influence the types of statements able to fire it.
For example, a trigger created at the database level can respond to statements related to tables, view interactions, and index management, as well as more specific statements to do with permissions management or statistics.
At the server level, we can use triggers for database management and to control server permissions and the use of credentials.
Don't worry about remembering exactly which DDL statements can be used to fire database- or server-level triggers; you can always check the online documentation from Microsoft for a full list of DDL events.
3. Database auditing
So what exactly can we do with database-level triggers?
Well, we can keep a trace of any activity happening at the database level. This is called a _database audit_.
We are going to use a group event to fire our "DatabaseAudit" trigger: DDL_TABLE_VIEW_EVENTS.
This DML event includes any operations dealing with tables, views, indexes, or statistics.
The advantage of using a group event is that you can specify a single event to cover all the cases that should fire the trigger (in this case, more than a dozen statements).
We are going to insert details about the operations performed in the database into the "DatabaseAudit" table.
The details of the operations will be extracted using the EVENTDATA function.
This function returns information about an operation in XML format. In order to extract specific information in clear text, we call the `value` function.
For example, the first call of the EVENTDATA and "value" functions will get the "EventType" from the XML and convert it to the SQL NVARCHAR data type.
The same logic is applied for the other columns.
4. Database auditing
Here's what the "DatabaseAudit" table looks like after using the trigger.
All the user actions are kept in the table. Any breaking changes can then be traced back to the responsible person.
5. Preventing server changes
Remember, it is not possible to use INSTEAD OF for DDL triggers.
But that doesn't mean you cannot prevent operations from happening when using DDL triggers.
DDL triggers are used in the real world to prevent database or server changes that could lead to data loss.
The trigger in this example prevents database deletion.
As you can see, it uses the ROLLBACK statement to prevent DROP operations on databases.
The statement that fired the trigger was prevented without the use of INSTEAD OF.
6. Let's practice!
The best way to understand use cases for DDL triggers is by practicing. Let's do that now!