Article Topics

All Articles

Performance Tuning

Audit

Business Intelligence

Clustering

Reporting Services

Developer

General DBA

ASP.NET / ADO.NET

Write for Us

Share your SQL Server knowledge with others and raise your profile in the community More...

DDL Triggers in SQL Server 2005

By : Dinesh Asanka
Aug 14, 2007

Page 3 / 3

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

ENABLE TRIGGER ddltrg_CREATE_TABLE_LOG

ON ALL SERVER

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.

<< Prev Page