x

How to ignore a trigger while another trigger is in use

I have the following trigger that uses a table name SOT (sales oreder table). Every time the table updated the trigger fires.

 CREATE TRIGGER TR_SOT  
 ON [MSN].[dbo].SOT FOR UPDATE AS 
 BEGIN
     SET NOCOUNT ON; DECLARE @cmd nvarchar(100); DECLARE @SaleOrderN nvarchar(100) = (select top 1 DNum  from inserted);
     IF (select top 1 GNum  from inserted) = '23'
         BEGIN
             SET @cmd = '\\SQL\apps\CreateTable.exe ' + @SaleOrderN 
             EXEC  master..xp_CMDShell @cmd , no_output
         END
 END
 GO

Thers is another table name DLT (delivery table) and I need to copy sales order to delivery table. Coping procees is fine but when I try to updated table DLT takes longer than normal time (30 seconds) because of TR_SOT trigger. I was wondering if I can add a trigger to DLT table so suppress/ignore (not disabling) the TR_SOT triger from being executed . Something like this.

 CREATE TRIGGER TR_DLT  
 ON [MSN].[dbo].DLT FOR UPDATE AS 
 BEGIN
     SET NOCOUNT ON;
     -- bypass/ignore/Suppress trigger TR_SOT
     -- code goes here
  END
 GO

Thank you for your help.

more ▼

asked Jan 09 at 05:05 PM in Default

avatar image

Allenb
220 3 8

Is the update for which you're trying to bypass the trigger many rows, or is it multiple single-row updates?

If it's one big update, then you could, at the start of the trigger, check to see how big the inserted table is, and skip the logic if it's more than, say, 100 rows...?

Jan 10 at 09:37 AM ThomasRushton ♦♦

Calling an executable from the trigger is not a good idea, you want to try and keep any logic in a trigger small and fast - otherwise it will start to affect other parts of the system - like blocking - like you are seeing.

To me it seems like you have too much business logic in triggers, and you may be better approaching this problem with a rethink around that.

Jan 10 at 11:13 AM Kev Riley ♦♦

Hi Thomas, How to skip the logic if there are more than 1000 rows? What is the query for it? Thanks for your help.

Jan 10 at 08:42 PM Allenb
(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x388

asked: Jan 09 at 05:05 PM

Seen: 93 times

Last Updated: Jan 10 at 08:42 PM

Copyright 2017 Redgate Software. Privacy Policy