Disable all triggers in a database

Question

How do I disable all triggers in a database from another database which are currently enabled .And then enable it back only the ones I disabled .

Answer 1

For DML triggers you can use below cursor. If you need to disable DDL triggers as well, you will need to create similar cursor base on sys.triggers view. (as in sys.objects we have only DML cursors)

declare @sql nvarchar(150)
declare trigger_cursor CURSOR FOR
select  'DISABLE TRIGGER ' + SCHEMA_NAME(schema_id)+'.'+name 
       +' ON '+SCHEMA_NAME(schema_id)+'.'+ OBJECT_NAME(parent_object_id)
    from sys.objects
    where type = 'TR'
OPEN trigger_cursor

FETCH NEXT FROM trigger_cursor INTO @sql
WHILE @@FETCH_STATUS = 0

BEGIN

      print @sql
      --sp_executesql @sql 
      FETCH NEXT FROM trigger_cursor INTO @sql 
END
CLOSE trigger_cursor

DEALLOCATE trigger_cursor

Answer 2

before disabling the triggers please consider a way to preserve the data integrity, for example, if there are 10 inserts coming up and some of them fire the triggers then you disable them triggers, then next inserts don't fire the triggers - then we have a problem now. if you don't consider this then the consequences can very likely be against you and\or your company.

you can for example, if your environment and work load and how busy your system is, set the database to single user mode.

at least think about the implications in your environment.

You can also script the triggers.

Script to enable/ disable triggers - script generation

    -- this script only returns the triggers 
    -- in an specific database or server triggers
use your_database
declare @TABLEIDOUT int=NULL -- this is the object_id of a table 
                             -- if not null then only the triggers for that 
                             -- table are considered
declare @include_server_triggers bit = 1 
       -- if 1 then the server triggers are 
       -- considered otherwise they are ignored
SELECT enable  =N'use ' + QUOTENAME(DB_NAME()) + N'; ENABLE TRIGGER ' 
                          + st.name + N' ON ' + st.parent_name
      ,disable =N'use ' + QUOTENAME(DB_NAME()) + N'; DISABLE TRIGGER ' 
                          + st.name + N' ON ' + st.parent_name
      ,name    = st.name
      ,ST.is_disabled
FROM (
SELECT 
             object_id=s.object_id
            ,parent_name  = QUOTENAME(OBJECT_SCHEMA_NAME(s.parent_id)) + '.' 
             + QUOTENAME(OBJECT_NAME(s.parent_id))
            ,name = QUOTENAME(OBJECT_SCHEMA_NAME(s.object_id)) + '.' 
             + QUOTENAME(OBJECT_NAME(s.object_id))
            ,s.is_disabled
            FROM sys.triggers s
            WHERE 1=1
             AND ((s.parent_id = @TABLEIDOUT) OR (@TABLEIDOUT is null))
) st
WHERE ST.name IS NOT NULL
UNION ALL
SELECT enable  =N'use master; ENABLE TRIGGER ' + QUOTENAME(st.name) 
                  + N' ON ALL SERVER'
      ,disable =N'use master; DISABLE TRIGGER ' + QUOTENAME(st.name) 
                  + N'ON ALL SERVER'
      ,name    = st.name
      ,ST.is_disabled
FROM master.sys.server_triggers st
WHERE @include_server_triggers =1 

you can check when triggers were disabled. it is not recorded in the default trace.