Microsoft® SQL Server® 2008 Bible

Chapter 27. DDL Triggers

IN THIS CHAPTER

• Creating DDL database triggers

• Preventing server or database changes

• DDL trigger scope

• Reading event data with XML

• Security triggers

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 EventData() function.

What's New with DDL Triggers

Introduced in SQL Server 2005, DDL triggers were extended to include logon triggers with SQL Server ...