Enable Disable All Triggers in a Database

,

Hi Friends,

This store procedure is used to Enable- Dsiable all triggers in a Database.

And to Show the triggers status use this query.

SELECT T.[name] as TableName, TR.[Name] as TriggerName,

CASE WHEN 1=OBJECTPROPERTY(TR.[id], 'ExecIsTriggerDisabled')THEN 'Disabled' ELSE 'Enabled' END Status

FROM sysobjects T INNER JOIN sysobjects TR on t.[ID] = TR.parent_obj WHERE (T.xtype = 'U' or T.XType = 'V')

AND (TR.xtype = 'TR') ORDER BY T.[name], TR.[name]

I hope this will help you. Leave your valuable comments.

Thanks for reading.

Vinay K

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].UDP_EnaDisTrigger') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].UDP_EnaDisTrigger
GO


/***************************************************************************************
Created By :- Vinay Kumar
Created on :- 04 Jan 2008
Purpose :- Enable and disable the all Database Triggers
****************************************************************************************/

SET NOCOUNT ON
GO

create Procedure UDP_EnaDisTrigger @check bit =0 
-- 0 for Disable Triggers
-- 1 for Enable Triggers
-- Default 0
as

if exists (select * from tempdb..sysobjects where name= '##trtemp' and xtype='u')
drop table ##trtemp


declare @maxid int
declare @string varchar(8000)
declare @tbname varchar(100)
declare @trname varchar(100)
Create table ##trtemp (trid int identity(1,1), Tabel_Name varchar(200))
insert into ##trtemp select name as tbname from sysobjects where id in(select parent_obj from sysobjects where xtype='tr')

select @maxid=max(trid) from ##trtemp
while (@maxid>=1)
begin 
    select @tbname=Tabel_name from ##trtemp where trid =@maxid

    if @check=0
        set @string ='Alter table '+ @tbname + ' disable trigger all'
    if @check=1
        set @string ='Alter table '+ @tbname + ' enable trigger all'
exec (@string)
--print @string
    set @maxid = @maxid-1
end

if @check=0
    select '" TRIGGERS ARE DISABLED "'
if @check=1
    select '" TRIGGERS ARE ENABLED "'



--exec udp_enadistrigger 1


SET NOCOUNT OFF
GO

Rate

4.38 (8)

Share

Share

Rate

4.38 (8)