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 (http://blog.SQLAuthority.com), BOL-Triggers

31 thoughts on “SQL SERVER – Disable All Triggers on a Database – Disable All Triggers on All Servers

  1. 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]

    Like

    • 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

      Like

      • 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

        Like

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

          Like

          • 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

            Like

        • 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

          Like

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

      Like

  2. Pingback: SQL SERVER - Disable All the Trigger of Current Database Journey to SQL Authority with Pinal Dave

  3. 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…

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

  10. Pingback: SQL SERVER – Significance of Various Kinds of Triggers- Quiz – Puzzle – 2 of 31 « SQL Server Journey with SQL Authority

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

    Like

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

    Like

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

    Like

  14. Pingback: SQL SERVER – Weekly Series – Memory Lane – #043 | Journey to SQL Authority with Pinal Dave

  15. 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 :)

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s