Understanding DDL triggers in SQL Server 2005
 Much like regular DML triggers, DDL triggers fire in response to an
event happening on the server. However, DDL triggers do not fire in
response to UPDATE, INSERT, or DELETE statements on a table or view.
Instead, they fire in response to Data Definition Language (DDL)
statements that start with the keywords CREATE, ALTER, and DROP. You can use DDL triggers to audit and regulate database operations.
For instance, you can write a DDL trigger to prevent certain changes to
your database schema, or maybe allow the change but record it in a log
table for auditing purposes. Certain system stored procedures that
perform DDL-like operations can also fire DDL triggers. The CREATE TYPE
statement and the sp_addtype stored procedure can both fire a DDL
trigger. However, not all system stored procedures fire DDL triggers.
The sp_rename stored procedure for instance does not fire any DDL
triggers. You need to test your DDL triggers to determine their
responses to system stored procedures. DDL triggers can be
server-scoped or database-scoped. A database-scoped DDL trigger is
simply called a database trigger. The following example shows how a
database trigger can be used to prevent any table in the current
database from being modified or dropped. Keep in mind that DDL triggers
fire only after the DDL statements that trigger them are run:
CREATE TRIGGER trigger1
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE AS
PRINT 'You cannot drop or alter this table.'
ROLLBACK You can temporarily disable a DDL trigger. Disabling a
DDL trigger does not drop it. The trigger will still exists as an
object; however, it will not be triggered by any of statements on which
it was programmed. DDL triggers that are disabled can be re-enabled.
Enabling a DDL trigger causes it to fire in the same way the trigger did
when it was originally created. DDL triggers do not fire in response
to events that affect local or global temporary tables and stored
procedures. Some DDL statements, like CREATE DATABASE, cannot be used
in DDL triggers. These statements are usually asynchronous,
non-transacted statements. |
