Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Disable triggers in SQL Server 2000

Posted on 2001-07-10
8
Medium Priority
?
629 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 400 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying 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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

927 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