Disable and enable trigger

Question

Disable and enable trigger

anbillava

Ten Centuries

Points: 1146
More actions
January 19, 2009 at 5:44 pm

#126773

Is there any way to enable and disable triggers during transaction. ?

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

Yes... there is. Please see "Enable Trigger" and "Disable Trigger" in Books Online.

Also, I'd be very, very wary of doing such a thing. The tirgger(s) were put on the table for a reason and disabling them may violate that reason. In practice, it's rarely a good thing to do.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

I agree with Jeff. The only time you may want to disable a trigger is when you are doing a mass load/update/delete that does not have to be covered by the purpose of a trigger (auditing, alerting, etc...).

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Dutta, Amartha SSC Eights! Points: 945 More actions · Answer 3

To disable / enable selective triggers...

ALTER TABLE tableName DISABLE TRIGGER triggername

ALTER TABLE tableName ENABLE TRIGGER triggername

To disable / enable all triggers...

ALTER TABLE tableName DISABLE TRIGGER ALL

ALTER TABLE tableName ENABLE TRIGGER ALL

Use this with caution and ensure proper handling as a transaction might have disabled but an error in the script might fail to enable it back.

Happy SQLing...

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

amartha_dutta (1/21/2009)
To disable / enable selective triggers...
ALTER TABLE tableName DISABLE TRIGGER triggername
ALTER TABLE tableName ENABLE TRIGGER triggername
To disable / enable all triggers...
ALTER TABLE tableName DISABLE TRIGGER ALL
ALTER TABLE tableName ENABLE TRIGGER ALL
Use this with caution and ensure proper handling as a transaction might have disabled but an error in the script might fail to enable it back.
Happy SQLing...

Heh... and the OP actually misses the opportunity to use Books Online for the first time in their life. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SG_Explorer Ten Centuries Points: 1339 More actions · Answer 5

IN SQL-2000

To disable / enable selective triggers...

ALTER TABLE tableName DISABLE TRIGGER triggername

ALTER TABLE tableName ENABLE TRIGGER triggername

To disable / enable all triggers...

ALTER TABLE tableName DISABLE TRIGGER ALL

ALTER TABLE tableName ENABLE TRIGGER ALL

*******************

IN SQL-2005

To disable / enable selective triggers...

DISABLE TRIGGER triggername ON tableName

ENABLE TRIGGER triggername ON tableName

To disable / enable all triggers...

DISABLE TRIGGER ALL ON tableName [optional:All server]

ENABLE TRIGGER ALL ON tableName [optional:All server]

anbillava Ten Centuries Points: 1146 More actions · Answer 6

I am able to do it by some of other posts in the portal. Thanks for all your info. I am actually working mas data archive and restore stuff. During that time if i enable triggers it leads to lot of other problems. So disabling trigger is must i feel.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 7

anbillava (1/23/2009)
I am able to do it by some of other posts in the portal. Thanks for all your info. I am actually working mas data archive and restore stuff. During that time if i enable triggers it leads to lot of other problems. So disabling trigger is must i feel.

And, if someone happens to try adding rows during your archive process, BOOM! Bad data because the trigger isn't active.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jizzy Wig SSChasing Mays Points: 601 More actions · Answer 8

What utter nonsense

altering a table within a transaction requires an exclusive lock on the table

so nobody can change the table in any way once the trigger is disabled (DDL or DML)

that lock will persist until the end of the transaction

This is basic database locking - if you don't know how locks work - you don't know how transactions work - and you don't know how databases work

This can be proved in 10 seconds by opening two query windows in SSMS and just typing:

[query1]

begin tran

alter table dbo.Container disable trigger trContainer

(execute)

[query2]

select * from dbo.Container

(execute)

-- note the lock wait ...

[query1]

rollback tran

(execute)

[query2]

oh look ... lock finished waiting results return

(execute)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 9

Jizzy Wig (3/20/2012)
What utter nonsense

Thanks for the example and no problem with disagreement but you really don't need to be that way.

Yes, if you have an explicit transaction or your settings are set to such a thing but a lot of folks forget about things like setting an explicit transaction when they disable triggers. You really don't need to be such a snot about it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 10

What utter nonsense

Thats no way to speak to such a helpful chap as Jeff 😛

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

Jizzy Wig SSChasing Mays Points: 601 More actions · Answer 11

I want to make sure people realise that enabling and disabling a trigger (usually as part of an archiving procedure) is completely normal and safe.

There is alot of disinformation about this.

There is even a totally ludicrous approach using set context_info by some muppet on stackoverflow

This post was #1 on google when I searched ... and it is not helping anybody.

(Your points are valid if people aren't using transactions properly)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 12

Jizzy Wig (3/21/2012)
I want to make sure people realise that enabling and disabling a trigger (usually as part of an archiving procedure) is completely normal and safe.
There is alot of disinformation about this.
There is even a totally ludicrous approach using set context_info by some muppet on stackoverflow
This post was #1 on google when I searched ... and it is not helping anybody.
(Your points are valid if people aren't using transactions properly)

It may be safe but I don't consider it to be "normal". The trigger is there for a reason and there really needs to be a very good reason to override it IMHO.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Anthony Scott SSC-Addicted Points: 408 More actions · Answer 13

Interesting discussion, we have come across this recently and are looking for a solution.

Might just have to try this, though not too certain about the rollback for the disable transaction, would this have the effect of rolling back all changes within that same transaction? Or can you nest transactions and have the inner ones totally autonomous to the wrapping transaction?

________________________________________________

We passed upon the stair - and I was that man who sold the world

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

And if I remember correctly when I had a need to disable and enable triggers in a closed process it had to be done using dynamic SQL as the disable and enable of the triggers had to the first (only?) statement(s) in the batch. Just as a note, this was also as part of an application upgrade so NO USERS where able to access the system while this process ran.