CODEWITHSUNDEEP
sqlsql-servertriggers
PriceCheaperton
PriceCheaperton

Reputation: 5349

DISABLE all triggers with a particular name on all tables using SQL

Id like to disable all triggers with the name beginning with TRG. I would like to loop through all my tables in my database.

So far I have this:

ALTER TABLE [dbo].[Customer] DISABLE TRIGGER TRG

Upvotes: 0

Views: 3617

Answers (2)

Andrew
Andrew

Reputation: 8703

The only thing I know is to use a query to build the alter table statements, and then copy/paste those:

        SELECT 
             sysobjects.name AS trigger_name 
            ,USER_NAME(sysobjects.uid) AS trigger_owner 
            ,s.name AS table_schema 
            ,OBJECT_NAME(parent_obj) AS table_name ,
            'ALTER TABLE ' + OBJECT_NAME(parent_obj) + ' DISABLE TRIGGER ' + sysobjects.name + ';' as Stmt

        FROM sysobjects 

        INNER JOIN sys.tables t 
            ON sysobjects.parent_obj = t.object_id 

        INNER JOIN sys.schemas s 
            ON t.schema_id = s.schema_id 
    WHERE sysobjects.type = 'TR' 
and sysobjects.name like 'TRG%'.

Upvotes: 3

Sean Lange
Sean Lange

Reputation: 33581

No need to loop here. That is the wrong mindset. You just need to leverage sys.triggers and build dynamic sql. Once you are comfortable with the output of @SQL uncomment the exec line.

declare @SQL nvarchar(MAX) = ''

select @SQL = @SQL + 'alter table [' + OBJECT_NAME(parent_id) + '] DISABLE TRIGGER ' + name + ';'
from sys.triggers
where name like 'trg%'

select @SQL

--exec sp_executesql @SQL

Upvotes: 2

Related Questions