:::: MENU ::::

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

  • Jul 02 / 2009
  • 0
dbDigger, SSMS tips and tricks, T-SQL Scripts, Triggers

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

Disable and enable trigger through SSMS

  • Right click on trigger and click on Disable.

Disable trigger through SSMS

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.

Enable trigger through SSMS

Again a confirmation dialog will appear and icon of trigger will be changed reflecting the change in status.

Consult us to explore the Databases. Contact us