none
DISABLE Trigger ALL on database not working RRS feed

  • Question

  • i have a program that reads a lot of data and writes a lot of data. I have some triggers that are fired that writes entires into an audit log we use for tracking. During this process i do not want those triggers fired.

     

    I first:

    DISABLE TRIGGER ALL ON DATABASE

     

    then i run all my insert and update commands (thousands of them)

     

    ENABLE TRIGGER ALL ON DATABASE

     

    But, while it is running all of my insert and update commands i look at the performance monitor and the trigger is still firing. Why?

    SQL 2008R2


    darin
    Sunday, April 24, 2011 6:23 PM

Answers

  • Check this helpful article

    http://msdn.microsoft.com/en-us/magazine/cc163442.aspx

    It looks like the syntax above disables all DDL triggers (not DML triggers).


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by KJian_ Monday, May 2, 2011 6:26 AM
    Sunday, April 24, 2011 6:49 PM
    Moderator
  • As Naomi pointed out, this syntax is for disabling/enabling DDL triggers on database level. You need to disable triggers for each involved table.

    But I would say that this is dubious. If it is a business requirement that your program should be excepted from auditing, this should be handled in the trigger itself. The trigger could be written as:

    IF object_id('tempdb..#big$update$in$progress') IS NOT NULL
       RETURN

    And in your update script you create this temp table, which thus acts as a global flag.

    The way you do now, someone else could sneak in and make an update without being audited.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by KJian_ Monday, May 2, 2011 6:24 AM
    Sunday, April 24, 2011 8:01 PM

All replies

  • Check this helpful article

    http://msdn.microsoft.com/en-us/magazine/cc163442.aspx

    It looks like the syntax above disables all DDL triggers (not DML triggers).


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by KJian_ Monday, May 2, 2011 6:26 AM
    Sunday, April 24, 2011 6:49 PM
    Moderator
  • As Naomi pointed out, this syntax is for disabling/enabling DDL triggers on database level. You need to disable triggers for each involved table.

    But I would say that this is dubious. If it is a business requirement that your program should be excepted from auditing, this should be handled in the trigger itself. The trigger could be written as:

    IF object_id('tempdb..#big$update$in$progress') IS NOT NULL
       RETURN

    And in your update script you create this temp table, which thus acts as a global flag.

    The way you do now, someone else could sneak in and make an update without being audited.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by KJian_ Monday, May 2, 2011 6:24 AM
    Sunday, April 24, 2011 8:01 PM
  • Hi Darin,

    We can disable all DML triggers by:

    EXEC sp_MSforeachtable @command1="ALTER TABLE ? DISABLE TRIGGER ALL"

    EXEC sp_MSforeachtable @command1="ALTER TABLE ? ENABLE TRIGGER ALL"


    "
    sp_MSforeachtable" gives us each table in database and replace "?" in above syntax.

    So query will be Disable/Enable Trigger on each table.

    NOTE: Make sure only use this query, if you want to DISABLE/ENABLE all table's triggers.

    Thanks,


    Tejas Shah
    SQL YOGA
    Monday, April 25, 2011 12:49 PM
  • The following page deals with the same topic:

    http://www.sqlusa.com/bestpractices2005/disabletriggerconstraint/

     


    Kalman Toth, SQL Server & BI Training; SQL GRAND SLAM
    Saturday, April 30, 2011 12:08 PM
    Moderator