Microsoft® SQL Server® 2012 Bible

904

Part VII: Monitoring and Auditing

■ (^) Caller: Executes as the person executing the DDL command that fi res the DDL
trigger
■ (^) Self: Executes as the person who created the DDL trigger
■ login_name: Executes as a specifi c login
Enabling and Disabling DDL Triggers
DDL triggers can be enabled or disabled. This is good because DBAs need an easy way to dis-
able DDL triggers that roll back any schema changes. The following code disables and then
enables the DDL_Create_Table_Sample trigger:
DISABLE TRIGGER DDL_Create_Table_Sample
ON DATABASE;
ENABLE TRIGGER DDL_Create_Table_Sample
ON DATABASE;
Removing DDL Triggers
DDL triggers aren’t listed in sys.objects, nor can their presence be detected using
object_id(). DDL triggers are listed in sys.server_triggers and sys.triggers
DMVs. Because DDL triggers can exist on either the database or server level, dropping them
requires a slightly different syntax:
IF EXISTS (SELECT
FROM sys.server_triggers
WHERE Name = 'DDL_Server_Level_Sample')
DROP TRIGGER DDL_Server_Level_Sample ON ALL SERVER
IF EXISTS (SELECT
FROM sys.triggers
WHERE Name = 'DDL_Database_Sample')
DROP TRIGGER DDL_Database_Sample ON DATABASE

Developing DDL Triggers

In some way, a DDL trigger is easier to write than a DML trigger. Because DDL triggers

always fi re for a single event, they avoid dealing with multiple rows involving the base

table the inserted and deleted virtual tables exposed in DML triggers. The complexity

of the DDL trigger results from the fact that the data about the event is in XML.

EventData()

DDL triggers can respond to so many different events that they need some method of cap-

turing data about the event that caused them to fi re. DML triggers have the inserted

c36.indd 904c36.indd 904 7/31/2012 10:03:10 AM7/31/2012 10:03:10 AM

http://www.it-ebooks.info