Microsoft® SQL Server® 2012 Bible

901

Chapter 36: Creating Triggers

36

DDL Triggers

DDL triggers are useful for auditing server-level and database changes. They can easily

pinpoint which objects were changed, who changed them, and even undo unauthorized

changes.

DDL triggers fi re as the result of some server-level or database schema–level event — typi-

cally data defi nition language (DDL) code — a CREATE, ALTER, or DROP statement. Where

DML triggers respond to data changes, DDL triggers respond to schema changes.

Just like DML triggers, DDL triggers can execute T-SQL code and can rollback the event.

Because DDL triggers can respond to so many types of events and commands, the

information about the event is passed to the trigger in XML using the EventData()

function.

Managing DDL Triggers

DDL triggers are easy to manage using normal DDL. The most signifi cant factor when devel-

oping a DDL trigger is the scope of the trigger — deciding which server- or database-level

events will fi re a trigger.

Creating and Altering DDL Triggers

DDL triggers are created or altered using syntax similar to working with DML triggers. The

location of the trigger, specifi ed by the ON clause, is either ALL SERVER or DATABASE. The

following code creates a database-level DDL trigger:

CREATE TRIGGER SchemaAudit

ON DATABASE

FOR DDL_Database_Level

AS

code

Server-level events are a superset of database-level events. They include all database level

events. The next example shows a server-level DDL trigger:

CREATE TRIGGER SchemaAudit

ON ALL SERVER

FOR DDL_Server_Level

WITH Options

AS

code

c36.indd 901c36.indd 901 7/31/2012 10:03:10 AM7/31/2012 10:03:10 AM

http://www.it-ebooks.info