Solved

Disable triggers in SQL Server 2000

Posted on 2001-07-10
8
599 Views
Last Modified: 2007-12-19
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.
0
Comment
Question by:CesarGon
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 3

Expert Comment

by:ibro
ID: 6268446
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
 
LVL 3

Expert Comment

by:ibro
ID: 6268452
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
 
LVL 3

Accepted Solution

by:
ibro earned 100 total points
ID: 6268477
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
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 
LVL 3

Author Comment

by:CesarGon
ID: 6268491
Thanks, ibro.

Is there any system-wide option instead of a per-table setting? If none appears, I'll give you the points.
0
 
LVL 3

Expert Comment

by:ibro
ID: 6268504
99.9 % no. I checked the online documents and I couldn't find any option to do this.
0
 
LVL 1

Expert Comment

by:mgrajkumar
ID: 6268540
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
 
LVL 3

Author Comment

by:CesarGon
ID: 6268549
Ok, ibro. I'll test your scripts and let you know.

Best regards,
Cesar.
0
 
LVL 3

Author Comment

by:CesarGon
ID: 6268705
Thanks ibro, and everybody else.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question