question

Allenb avatar image
Allenb asked

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.
sql-server-2012
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
1 Like 1 ·
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...?
0 Likes 0 ·
Hi Thomas, How to skip the logic if there are more than 1000 rows? What is the query for it? Thanks for your help.
0 Likes 0 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.