TABLE OF CONTENTS This chapter introduces DDL triggers. It explains what they are, demonstrates how to implement them, and shows you when to implement them. It also provides real-world examples of how and when DDL triggers can be used to help secure an SQL Server.
Data Definition Language (DDL) is the subset of T-SQL instructions and statements that define structure, whether that structure is objects like tables and views, schemas, or security principals like server logins and database users. Although triggers have been a part of the SQL Server product, they only applied to certain Data Manipulation Language (DML) instructions, including INSERT, UPDATE, and DELETE. Until this latest version of SQL Server, there was no capability to fire a trigger on a DDL statement (such as DROP TABLE).
Since SQL Server 2000, triggers can fire either before a statement executes, intercepting that statement, or afterward. Triggers that fire before a statement executes are called INSTEAD OF triggers, because they execute "instead of" the statement itself. Triggers that fire after the statement executes (but before the transaction or batch process completes) are called AFTER triggers. AFTER triggers were available prior to SQL Server 2000, and as a result, represent the default trigger behavior. DDL triggers can only be defined as AFTER triggers. They do not intercept the DDL statement before it executes. However, triggers, including DDL triggers, do provide the capability of rolling back the statement. You can't stop a DDL statement...
