Creating DDL Triggers

The basic syntax for creating a DDL trigger follows:

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

CREATE TRIGGER tr_TableAuditON DATABASEFOR CREATE_TABLE, ALTER_TABLE, ...

Get Microsoft® SQL Server 2012 Unleashed now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.