TSQL - Disabling Triggers in Transactions

Question

I've search high and low, and I haven't been able to find a satisfactory answer to my question. Which tends to boil down to how exactly a TRANSACTION works in SQL Server.

Basically, will this do what I think it does.

BEGIN TRAN

DISABLE [my_update_trigger] ON [my_table]

/*.. Do Some Updates ..*/

ENABLE [my_update_trigger] ON [my_table]

COMMIT TRAN

I want to be able to fix some data in a table, without running the update triggers I have on the table. This is for a web app, so I'd like to make sure that if an update is done on the table from the web app, while I'm doing my work, [my_update_trigger] will still fire for the web app.

Solution 1

The update stuff is ok - the disable enable etc.

DISABLE TRIGGER [my_update_trigger] ON [my_table]

/*.. Do Some Updates ..*/

ENABLE TRIGGER [my_update_trigger] ON [my_table]

Have a look at the msdn page: http://msdn.microsoft.com/en-us/library/ms189748.aspx

On making it session specific though: I'd doubt if that would work - the disable/enabled are DDL rather than DML, ie they act on the database objects rather than the data. I wouldn't have thought this would be in the scope of a Transaction

Solution 2

Semicolons work for me:

BEGIN TRAN

;DISABLE [my_update_trigger] ON [my_table]

/*.. Do Some Updates ..*/

;ENABLE [my_update_trigger] ON [my_table]

COMMIT TRAN

Solution 3

I did some tests and, apparently, your code does exactly what you want it to do. Here is my test protocol:

   -- Session 1 --                                   -- Session 2 --
1. BEGIN TRANSACTION
2. DISABLE TRIGGER [my_update_trigger] ON [my_table]
3.                                                   UPDATE my_table SET x = y   -- blocks
4. ROLLBACK
                                                     -- unblocks *and triggers the trigger*

In addition, SELECT is_disabled FROM sys.triggers where name = 'my_update_trigger'; reveals that the trigger is disabled after step 2 and enabled after step 4.

Thus, my conclusion is:

• Yes, enabling/disabling triggers is "global" and not "per session" (as can be seen by querying sys.triggers), but
• SQL Server's locking mechanisms ensure that disabling a trigger in a transaction locks the table, so other sessions won't "miss" their trigger.

---

A word of caution: DISABLE TRIGGER won't trigger an implicit transaction. This is important if you are using (classic) ADO, since Connection.BeginTrans won't start an explicit transaction but will rather SET IMPLICIT_TRANSACTION ON. You need to perform one of the operations listed here before disabling the trigger, or the DISABLE TRIGGER statement will be outside the transaction.

Solution 4

if you want to execute ENABLE TRIGGER Directly From Source :

we can't write :

Conn.Execute "ENABLE TRIGGER trigger_name ON table_name"

instead, we can write :

Conn.Execute "ALTER TABLE table_name DISABLE TRIGGER trigger_name"

View more solutions