— exec EnableOrDisableTriggers_prc 1

create Procedure EnableOrDisableTriggers_prc
@check bit =0
— 0 for Disable Triggers
— 1 for Enable Triggers

as

if exists (select * from tempdb..sysobjects where name= ‘##TempTable’ and xtype=’u’)
drop table ##TempTable

— select parent_obj from sysobjects where xtype=’tr’

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

select @maxid=max(trid) from ##TempTable
while (@maxid>=1)
begin
select @tbname=Tabel_name from ##TempTable 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 not exists (select top 1 1 from ##TempTable)
select ‘There are no trigger on the database!’
else
begin
if @check=0
select ‘Triggers are disabled’
if @check=1
select ‘Triggers are enabled’
end