Disable triggers in SQL Server 2000

Hi all.

How can I temporarily disable all triggers in a server or connection-wide basis? Is there any system configuration option?

Best regards,
Cesar.
LVL 3
CesarGonAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
ibroConnect With a Mentor Commented:
Your final script may look like this:

for disable:
select  'alter table '+s2.name+' disable trigger '+s1.name from sysobjects s1,
(select id, name from sysobjects where type='U') s2
where s1.parent_obj=s2.id and s1.type='TR'

for enable:
select  'alter table '+s2.name+' enable trigger '+s1.name from sysobjects s1,
(select id, name from sysobjects where type='U') s2
where s1.parent_obj=s2.id and s1.type='TR'

After you see the result of the upper queries, you can copy/past the results run them.

0
 
ibroCommented:
You can disable trigger for specified table with statement.

alter table my_table disable trigger all
or
alter table my_table disable trigger my_trigger

You can get trigger list with
select  s1.name TriggerName, s2.name TableName f
rom sysobjects s1,
(select id, name from atadb..sysobjects where type='U') s2
where s1.parent_obj=s2.id

So you can construct automatic script for disable/enable triggers.
0
 
ibroCommented:
oopps... the second statement shall be
select  s1.name TriggerName, s2.name TableName f
rom sysobjects s1,
(select id, name from atadb..sysobjects where type='U') s2
where s1.parent_obj=s2.id and s1.type='TR'
0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
CesarGonAuthor Commented:
Thanks, ibro.

Is there any system-wide option instead of a per-table setting? If none appears, I'll give you the points.
0
 
ibroCommented:
99.9 % no. I checked the online documents and I couldn't find any option to do this.
0
 
mgrajkumarCommented:
Hi
      There is no server side option to disable or enabling the triggers.Below  code will disable all the triggers will meet ur requirements

Create Procedure Sp_EnableTriggers as

     Declare @strTableName  Varchar(255)
     Declare @StrSQL  varchar(255)

     DECLARE EnableTrigger   CURSOR FOR
     Select  object_name(parent_obj)  from  sysobjects  where type='tr'
     OPEN EnableTrigger
     FETCH NEXT FROM EnableTrigger  INTO @strTableName
        WHILE (@@fetch_status <> -1)
         BEGIN
        Select @strSQL='Alter Table ' +@strTableName + ' Enable Trigger all'
        EXEC (@strSQL)
        FETCH NEXT FROM EnableTrigger  INTO @strTablename
           CONTINUE
      END
     DEALLOCATE  EnableTrigger
0
 
CesarGonAuthor Commented:
Ok, ibro. I'll test your scripts and let you know.

Best regards,
Cesar.
0
 
CesarGonAuthor Commented:
Thanks ibro, and everybody else.
0
All Courses

From novice to tech pro — start learning today.