Microsoft® SQL Server 2012 Unleashed

Creating DDL Triggers

The basic syntax for creating a DDL trigger follows:

Click here to view code image

CREATE TRIGGER trigger_nameON { ALL SERVER | DATABASE }[ WITH <ddl_trigger_option> [ ,...n ] ]{ FOR | AFTER } { event_type | event_group } [ ,...n ]AS { sql_statement  [ ; ] [ ...n ] | EXTERNAL NAME < method specifier >  [ ; ] }

The best way to illustrate the use of the DDL trigger syntax and power of these triggers is to look at a few examples. The example shown in Listing 26.9 illustrates the creation of a DDL trigger that is scoped at the database level and prevents table-level changes.

LISTING 26.9 A Database-Scoped DDL Trigger for Tables

Click here to view code image

CREATE TRIGGER tr_TableAuditON DATABASEFOR CREATE_TABLE, ALTER_TABLE, ...