Microsoft® SQL Server™ 2008 All-In-One Desk Reference For Dummies®

IV.3.2. DDL Triggers: Letting Your Server or Your Database Look After Itself

Data Definition Language (DDL) triggers have been in SQL Server since SQL Server 2005. Like Data Manipulation Language (DML) triggers, DDL triggers can't be called directly. Instead, they respond to DDL events. DDL events are created when DDL statements are executed. DDL statements that can fire triggers include

• CREATE: The CREATE statement is used to create objects, such as tables, databases, and logins.

• ALTER: The ALTER statement is used to make modifications to objects.

• DROP: The DROP statement is used to remove objects.

• GRANT: The GRANT statement is used to grant permissions.

When you're removing data, you use the DELETE statement. When you're removing objects, you use the DROP statement. UPDATE is used to modify data, whereas ALTER is used to modify objects. It's a subtle difference, but the statement being used (DML or DDL) helps you identify whether you're working on data or working on objects.

DDL triggers have two possible scopes: database and server. The scope identifies the type of objects that the trigger will monitor.

• Database scope: A trigger with a database scope monitors objects within a specific database. These include tables, views, stored procedures, functions, or any other object within a database that can be manipulated.

• Server scope: A trigger with a server scope monitors any objects ...