O'Reilly logo

Microsoft® SQL Server® 2008 Bible by Uttam Parui, Mike White, Paul Nielsen

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required