Solved

Disable triggers in SQL Server 2000

Posted on 2001-07-10
8
593 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Some sers suddenly getting error popup msg 28 86
Sql Stored Procedure field variable 17 27
Get the latest status 8 29
ms sql + get number in list out of total 7 26
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

808 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