Wednesday, November 4, 2009

How To Disable and Enable Triggers In SQL Server

In our Project, we’ve created Trigger for generate the transactional log. This log will be updated, whenever new data has been “inserted” or “updated or deleted” the Existing Record by using application or manually by executing T-SQL queries via SQL Client tool.

Recently, we’ve planned to import huge data thru ETL from other databases. In this scenario, trigger will update all the data to transactional log table. So we don’t want keep this record in our transactional log. To avoid this, we planned to disable the trigger in our database.

I followed the given steps to disable/Enable the trigger.

To Find the List of Triggers, you can use the following Query.

SELECT * FROM sys.triggers

The given Query will list all the triggers, which are all existed in your current Databases as well as other information about the triggers.

“sys.triggers” System Table contains a column, Named as “is_disabled”, this column holds the status of the trigger, either enabled or disabled.

is_disabled = 0 Means Enabled
is_disabled = 1 Means Disabled

There are 2 ways to disable a trigger by following ways

  1. T-SQL Script
  2. SQL Server Management studio Dialog

To Disable a Trigger By T-SQL Script:


After Disabled, if you run this query, you can ensure that you disabled the proper trigger or not.

SELECT * FROM sys.triggers

To Disable a Trigger By SQL Server Management studio (SSMS) Dialog:

  1. Expand the Table node in SSMS, Which is associated with Trigger
  2. then select the trigger as shown in the exhibit and select disable

After Trigger has been disabled, trigger icon will change with Red down Arrow, as shown in the figure.

To Enable a Trigger By T-SQL Script:


To Enable a Trigger By SQL Server Management studio Dialog:

  1. Right click on the disabled trigger, then click “Enable”

Icon will be changed to normal mode, after enabled the trigger.

Hope it helps you!

1 comment: