Sql server® 2012 t-sql fundamentals

ddL Triggers

SQL Server supports DDL triggers, which can be used for purposes such as auditing, policy enforce-

ment, and change management. On-premises SQL Server supports the creation of DDL triggers at 

two scopes, the database scope and the server scope, depending on the scope of the event. SQL 

Database currently supports only database triggers. 

You create a database trigger for events with a database scope, such as CREATE TABLE. You create 

an all server trigger for events with a server scope, such as CREATE DATABASE. SQL Server supports 

only after DDL triggers; it doesn’t support instead of DDL triggers.

Within the trigger, you obtain information on the event that caused the trigger to fire by querying 

a function called EVENTDATA that returns the event information as an XML value. You can use XQuery 

expressions to extract event attributes such as post time, event type, and logon name from the XML 

value.

www.it-ebooks.info

---