Thursday, July 2, 2009

Enable or disable a DML Trigger through T-SQL and SSMS

I have created triggers to track changed data in various fields of several tables. Such triggers work at table level and are attached with table. While performing schedule updates through SQL Server agent jobs, i disable the triggers in the stored procedure that is performing given updates. It prevents the extra usage of triggers and also optimizes the space by skipping entries by such triggers.
Here is syntax to disable and enable trigger1 on table1
-- Disable the trigger
ALTER TABLE table1 DISABLE TRIGGER trigger1
GO

-- Enable the trigger
ALTER TABLE table1 ENABLE TRIGGER trigger1
GO

Similarly another syntax may be used to enable and disable our imaginary trigger trigger1 on imaginary table table1 in this case
-- Disable the trigger
DISABLE TRIGGER trigger1 on table1
GO

-- Enable the trigger
ENABLE TRIGGER trigger1 on table1
GO

Same task may be performed very easily through SSMS. Suppose trigger trigger1 is created on table table1. And we have to disable and enable it through SSMS
  • Open SSMS
  • Go to table on which trigger is defined
  • Open triggers folder under table


  • Right click on trigger and click on Disable.

Confirmation dialog will be shown and icon of trigger will be slightly changed showing the change in trigger status. To enable the trigger through SSMS, again right click on disabled trigger and click Enable.

Again a confirmation dialog will appear and icon of trigger will be changed reflecting the change in status. Click here for creation of a basic trigger for update audit.

0 comments:

Post a Comment

Express your views about this post