DDL Triggers in SQL Server 2005

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.

Pages: 1 2 3




Related Articles :

  • No Related Articles Found

2 Responses to “DDL Triggers in SQL Server 2005”

  1. There is a litle mistake on your code just a tiny thing when you do the “insert” instruction the name of the database you use it’s diferent than the one you created before

    DDL_TRIGGERS_DB-> DDL_Trigger_Log.dbo.tblDDLEventLog

    Thanks for the article it helped me a lot

  2. For user it’s better to use user_name .. because that is the logged in user iso the role, besides this it’s very very helpfull

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |