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.