Disable and enable trigger

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

  • 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)

  • 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...).

  • 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...

  • 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)

  • 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]

  • 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.

  • 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)

  • 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)

  • 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)

  • 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

  • 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)

  • 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)

  • 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
  • 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.

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

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