Enable or Disable Triggers
As in DML triggers, you have the option to Enable or Disable DDL triggers (for both server and database triggers)
DISABLE TRIGGER ddltrg_CREATE_TABLE_LOG
ON ALL SERVER
GO
ENABLE TRIGGER ddltrg_CREATE_TABLE_LOG
ON ALL SERVER
GO
Trigger Execution Order
When there are several triggers, you can define which trigger to execute first and last. There is a system stored procedure named sp_settriggerorder to set the priority. This is the same stored procedure which you can use to set priority for DML triggers as well.
|
sp_settriggerorder [ @triggername = ] ‘[ triggerschema. ] triggername‘ , [ @order = ] ‘value‘ , [ @stmttype = ] ‘statement_type‘ [ , [ @namespace = ] { ‘DATABASE’ | ‘SERVER’ | NULL } ] |
From @order parameter you can set either first or last, which is the order of the trigger execution. The @namespace parameter can be set either DATABASE or SERVER depending on whether the DDL trigger is a database or server dependent trigger.
System Tables
It is often necessary to know where the triggers are saved. In case of database DDL triggers, the information is stored in sys.triggers and sys.trigger_events. The sys.triggers view contains information like trigger name, create date etc and sys.trigger_events view contains the for which events those triggers are going to execute.
SELECT *
FROM sys.triggers
SELECT *
FROM sys.trigger_events
In case of Server DDL triggers, you have to use sys.server_triggers and sys.server_trigger_events.
SELECT *
FROM sys.server_triggers
SELECT *
FROM sys.server_trigger_events
Improvements
Eventhough there are 100+ events included for DDL triggers, there are few important events. Specifically events for database backup, database restore, and SQL Server Job related.
]]>