Post reply

Inactivate a trigger in sql2000

  • June 18, 2002 at 9:27 am

    #357554

    Hi, good morning,

    I hope everybody is OK, bothering you about a topic. . . my question is how to inactivate a trigger in sql2000 ? is there any instruction for doing that ? I'm finding out about this because I have to do some modifications to the database and I really need the triggers don't fire.

    thanks a lot for your comments, bye !

    Amaury


    L.I. Amaury C.R.

  • June 18, 2002 at 10:29 am

    #431054

    This should work for you:

    ALTER TABLE table_name DISABLE TRIGGER trigger_name

    This statement should print out the commands you need to run for all active triggers in the database:

    select 'ALTER TABLE ' + b.name + ' DISABLE TRIGGER ' + a.name from sysobjects a join sysobjects b on a.parent_obj=b.id where a.type = 'tr' AND A.STATUS & 2048 = 0

    Enjoy,

    DanW

  • June 18, 2002 at 11:06 am

    #431059

    Hi again !

    Thanks a lot for repplying my message, this is going to be very useful for me. It's obvious that the opposite command is ENABLE TRIGGER, isn't it? . . .

    And the select is very useful too 'cause I can disable or enable or the objects I want, just a small question, I know these are bits operations, but why 2048 ?

    thanks a lot again ! ! ! = )


    L.I. Amaury C.R.

  • June 18, 2002 at 11:17 am

    #431062

    The 2048 bit is what SQL Server is setting behind the scenes when you disable a trigger. So the sql is checking to make sure this is not set with status & 2048 = 0. You could also do this the other way around and check for just the triggers that are disabled with status & 2048 = 2048.

    The method I used to find out what bit controls the ENABLE\DISABLE status is by comparing the value before and after disableing a trigger.

    oh and yes you are right that changing the command to ENABLE will enable the constraints.

    DanW

  • June 18, 2002 at 11:27 am

    #431063

    THANKS A LOT ! ! ! ! !

    AMAURY

    amaurycrz @ hotmail.com

    = )


    L.I. Amaury C.R.

  • June 18, 2002 at 7:41 pm

    #431105

    Hi

    I would test this very carefully, from memory here I had a senario some time back where the trigger continued to fire. I will have to re-test.

    Cheers

    Ck


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

Viewing 6 posts - 1 through 5 (of 5 total)

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