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

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.

Answer 2

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