IN THIS CHAPTER
Creating DDL database triggers
Preventing server or database changes
DDL trigger scope
Reading event data with XML
Sometimes the difference between an endless search to identify the problem and readily solving the problem is as simple as knowing what changed. DDL triggers are perfect for auditing server-level and database changes.
Why devote a whole chapter to DDL triggers when the material only takes a few pages? Because I'm convinced every database, development, test, or production, should have a schema audit DDL trigger.
A trigger is code that executes as the result of some action. DML triggers fire as the result of DML code—an
INSERT, UPDATE, DELETE, or
MERGE statement. DDL triggers fire as the result of some server-level or database schema–level event—typically data definition language (DDL) code—a
CREATE, ALTER, or
DROP statement. DML triggers respond to data changes, and DDL triggers respond to schema changes.
Just like DML triggers, DDL triggers can execute T-SQL code and can rollback the event. DML triggers can see into the data transaction using the inserted and deleted tables. Because DDL triggers can respond to so many types of events and commands, the command that fired the trigger and other appropriate information about the event is passed to the trigger in XML using the