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
Solved

Disable triggers in SQL Server 2000

Posted on 2001-07-10
8
595 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
  • 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

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.
I have a large data set and a SSIS package. How can I load this file in multi threading?
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

838 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