SQL SERVER – Disable All Triggers on a Database – Disable All Triggers on All Servers

Just a day ago, I received question in email regarding my article SQL SERVER – 2005 Disable Triggers – Drop Triggers.

Question : How to disable all the triggers for database? Additionally, how to disable all the triggers for all servers?
Answer:
Disable all the triggers for a single database:
USE AdventureWorks;
GO
DISABLE
TRIGGER Person.uAddress ON AdventureWorks;
GO

Disable all the triggers for all servers:
USE AdventureWorks;
GO
DISABLE
TRIGGER ALL ON ALL SERVER;
GO

Reference : Pinal Dave (https://blog.sqlauthority.com), BOL-Triggers

, ,
Previous Post
SQL SERVER – 2008 – Server Consolidation WhitePaper Download
Next Post
SQL SERVER – Business Intelligence (BI) Basic Terms Explanation

Related Posts

28 Comments. Leave new

  • How would

    DISABLE TRIGGER Person.uAddress ON AdventureWorks;

    disable all triggers on a database? Person.uAddress is right there

    Reply
  • Hi Pinal
    We use SQL Server 2000. I created a trigger, trying to update a field after a record is inserted.

    Reply
  • Manish Kaushik
    August 18, 2008 11:00 am

    To disable all the trigger of current database.

    CREATE Procedure [dbo].[DisableAllTriggers]

    AS
    Declare @string varchar(8000)
    Declare @tableName nvarchar(500)
    Declare cur cursor
    for select name as tbname from sysobjects where id in(select parent_obj from sysobjects where xtype=’tr’)
    open cur
    fetch next from cur into @tableName
    while @@fetch_status = 0
    begin
    set @string =’Alter table ‘+ @tableName + ‘ Disable trigger all’
    exec (@string)
    Fetch next from cur into @tableName
    end
    close cur
    deallocate cur

    –To execute the SP—

    Exec [DisableAllTriggers]

    Reply
    • Hi Manish,

      Please help me HERE
      I tried your proc for the disabling of triggers at database level.
      I created this proc successfully,but while executing i got the error as below:

      Msg 343, Level 15, State 1, Line 1
      Unknown object type ‘tableSalesOrderDetailDisable’ used in a CREATE, DROP, or ALTER statement.
      Msg 343, Level 15, State 1, Line 1
      Unknown object type ‘tableSalesOrderHeaderDisable’ used in a CREATE, DROP, or ALTER statement.

      Please advice thanks a lot

      Reply
      • make sure you have a space after the single quote and disable on:
        @tableName + ‘ Disable
        You probably have a table named “tableSalesOrderDetail” and is being concatenated with the word “Disable” (no object exists with that name), should result in
        “Alter table tableSalesOrderDetail Disable trigger all”, not
        “Alter table tableSalesOrderDetailDisable trigger all”.

        Hope it helps

      • Hi Helder,

        Thanks a lot for your reply
        after i left the space as you said..i am getting the following error.

        Msg 4902, Level 16, State 1, Line 1
        Cannot find the object “Store” because it does not exist or you do not have permissions.
        Msg 4902, Level 16, State 1, Line 1
        Cannot find the object “Vendor” because it does not exist or you do not have permissions.
        Msg 4902, Level 16, State 1, Line 1
        Cannot find the object “PurchaseOrderDetail” because it does not exist or you do not have permissions.
        Msg 4902, Level 16, State 1, Line 1
        Cannot find the object “PurchaseOrderHeader” because it does not exist or you do not have permissions.

        Please help me ,thanks a lot.

      • Hi,

        I guess the user you are using to execute does not have permissions to ALTER those tables (as the message states).
        Either login with “sa” and execute the procedure, or grant the user you’re logging in with alter table permissions on those tables.

        Be carefull if you have more than one database on the server, if you run the procedure “as is”,
        it will disable all triggers for ALL tables on ALL databases.

        Hope it helps

      • Hi Helder,

        Thanks for help.I was run the SP for multiple other schema i madde a change in schema to dbo now it looks fine.
        Thanks a lot for your responses

    • Hi manish,

      I am getting this error.
      Please help me
      Msg 4902, Level 16, State 1, Line 1
      Cannot find the object “Store” because it does not exist or you do not have permissions.
      Msg 4902, Level 16, State 1, Line 1
      Cannot find the object “Vendor” because it does not exist or you do not have permissions.
      Msg 4902, Level 16, State 1, Line 1
      Cannot find the object “PurchaseOrderDetail” because it does not exist or you do not have permissions.
      Msg 4902, Level 16, State 1, Line 1
      Cannot find the object “PurchaseOrderHeader” because it does not exist or you do not have permissions.
      Msg 4902, Level 16, State 1, Line 1
      Cannot find the object “WorkOrder” because it does not exist or you do not have permissions.
      Msg 4902, Level 16, State 1, Line 1
      Cannot find the object “SalesOrderDetail” because it does not exist or you do not have permissions.

      Reply
  • Thanks a lot… Manish Kaushik!

    Reply
  • hi Mr Dave
    i’ve seen your articles and are great, i need to create a script which will calculate a value based on previous value i.e ID Depreciation
    51004 0
    54796 0
    58585 -19079.347
    62466 -113.57
    66291 -113.57
    70143 -113.57
    74025 -113.57
    77933 -113.57
    81849 -113.57
    on my script i need to calculate a Accummulative Depreciation on another field and the rule is on the first entry the Depreciation will remain the same, the second entry should take the value from the prevous entry add it to the current entry i.e for ID 51004 and 54796 AccumDepr will be 0 cause there’s no Depreciation value this id 58585 should equal to 19079.347 cause there’s no previous depr, and on ID 62466 my AccumDepr should be (-19079.347) + (-113.57) = -19192.91 and the next ID should be -19192.91 + -113.57 = -19306.48 and so forth…

    Reply
  • Good Day! I’m a beginner with regards to triggers. Currently, I’ve already created a couple of triggers that are working just fine. But my problem is that one of our clients bought a new server. They want to transfer some databases in the new server and that would affect the triggers that are currently working. Is it possible for the trigger to work in different servers? What will be the syntax for that?
    Thanks in advance.

    Reply
  • Hi all,

    To enable & disable all the triggers and constraints we have a system defined Stored Proc

    Print ‘Disabling all Constraints’
    exec sp_MSforeachtable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’

    Print ‘Disabling all Triggers’
    exec sp_MSforeachtable ‘ALTER TABLE ? DISABLE TRIGGER ALL’

    Print ‘Enabling all Constraints’
    exec sp_MSforeachtable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’

    Print ‘Enabling all Triggers’
    exec sp_MSforeachtable ‘ALTER TABLE ? ENABLE TRIGGER ALL’

    I hope this helps.

    Thanks
    Vishal Kapoor

    Reply
  • Hi Pinal,

    I am a beginner. In an interview I have been asked how to know whether a trigger is working ? or how to know whether the trigger will be fired at the correct time?
    can you please help me…

    thanks in advance.

    Reply
  • feras mazen taleb
    June 15, 2009 4:41 pm

    thanks alot mr Manish Kaushik

    the Procedure DisableAllTriggers works well

    But Could you let me add small notice :)

    we have to Add [ and ] to the table name I tried to
    exceute this procedure on my database and error occured
    because of my table name [References] this is a key word

    set @string =’Alter table [‘+ @tableName + ‘] Disable trigger all’

    Reply
  • Hi Manish,

    This query

    select name as tbname from sysobjects where id in(select parent_obj from sysobjects where xtype=’tr’)

    that you have used to retrieve the list of tables in the database is not correct.

    The query you have provided will include tables and views and the Alter table statement will not work with a view.

    I would use the following query to retrieve just the table names.

    select table_name from information_schema.tables where table_type = ‘base table’

    Thanks

    Reply
  • Hi All ,

    To enable and disable all the trigger we can directly use

    DISABLE TRIGGERS ALL ON ALL SERVER ;

    Reply
  • I love one line commands that do everything !

    DISABLE TRIGGER ALL ON ALL SERVER;

    Like this for indexes as well

    sp_MSforeachtable @command1=”print ‘?’ DBCC DBREINDEX (‘?’)”

    Cheers on all the great content

    Reply
  • Jerry Hung II
    March 23, 2012 8:28 pm

    Keep in mind that this article is About DDL triggers, following script will generate sql statement for disabling triggers

    SELECT ‘Disable Trigger ‘+name+’ On ‘+OBJECT_NAME(parent_id) FROM sys.triggers

    Reply
  • is there any way on how to change the database name in all existing triggers on a database.

    Reply
    • Simple method is to Script out the triggers and do find and replace

      Reply
      • hi.Thanks for the response.

        Any idea how i can do it, mean how to script out the triggers. I know you can select * from sys.triggers.

  • Helder Gramacho
    January 23, 2013 7:11 pm

    Hi to all.
    To disable all DML triggers on all tables and VIEWS, i’ve improved Manish Kaushik’s procedure:

    CREATE Procedure [dbo].[DisableAllTriggers]
    AS
    Declare @string varchar(8000)
    Declare @tableName nvarchar(500)
    Declare @triggerName nvarchar(500)
    Declare cur cursor
    for select s.name as tbname, t.name as trname FROM sys.triggers t
    inner join sysobjects s
    on (s.id = t.parent_id)
    where s.xtype=’V’
    exec (‘sp_msforeachtable “ALTER TABLE ? DISABLE TRIGGER all”‘
    open cur
    fetch next from cur into @tableName, @triggerName
    while @@fetch_status = 0
    begin
    set @string =’DISABLE TRIGGER ‘+ @triggerName + ‘ ON ‘ + @tableName
    print @string
    exec (@string)
    Fetch next from cur into @tableName, @triggerName
    end
    close cur
    deallocate cur

    go

    Exec [dbo].[DisableAllTriggers]
    go
    drop procedure [dbo].[DisableAllTriggers]
    go

    Reply
    • Helder Gramacho
      January 23, 2013 7:17 pm

      Forgot to add the closing parenthesis on the line:
      exec (‘sp_msforeachtable “ALTER TABLE ? DISABLE TRIGGER all”‘)

      Reply
  • Hello Pinal Sir
    i have a question i want to block drop table permission on database to specific user please suggest me the way i can achieve this please remember one thing user have access to alter but not drop table..

    Amit Jha

    Reply
  • its easy use trigger on that table or database..

    Reply
  • Hi there !
    I’m in a situation where i have to disable all my triggers and enable them in the and of my specific backup.
    I kept a table #InitiallyDesabledTR(object_ID, Name) with the object_id and the name of all my triggers that are initially disabled.
    Did any one have an idea on how could i re-enable all my triggers except those present on my table InitiallyDesabledTR.
    Thanks for responding :)

    Reply
  • I have created few Triggers on ALL SEVER
    no how do I view a Trigger created on “ALL SERVER” using management studio or SQL Query.

    Reply

Leave a Reply

Menu