1

I'm using SQL SERVER 2014.

I have a table where it has 2 triggers (for update).

Trigger 1 - updates columns A. (which causes Trigger 2 to be invoked)

Trigger 2 - updates columns B. (which causes Trigger 1 to be invoked)

Question:

Within a trigger - is it possible to identify that the current update was triggered via a Trigger ?

Example :

1- User updated the table
2- Trigger 1 updates column A
3 -Trigger 2 invoked due to step 2.
4 - within Trigger 2 - is there any code that can detect that update was made via trigger in step2 ?

Royi Namir
  • 144,742
  • 138
  • 468
  • 792
  • add a field to the table and update it in trigger 1. Than trigger 2 can read that field – GuidoG Sep 19 '17 at 10:26
  • @GuidoG Nice work around - but adding an indication columns for all of my tables (~500) seems problematic.... :-) – Royi Namir Sep 19 '17 at 10:27
  • Does column A only gets updated by trigger 1 .? If so than you can check in trigger 2 if inserted.A is different than deleted.A – GuidoG Sep 19 '17 at 10:28
  • @GuidoG No . it can also be updated via the App itself. I jsut want to prevent recursion of updated for those two sibling triggers. something like (within trigger 2) - `If triggered_via_trigger() return` – Royi Namir Sep 19 '17 at 10:30
  • Then I dont have answer for you. I dont think it is possible, good luck – GuidoG Sep 19 '17 at 10:31
  • @GuidoG Apprently there's a solution. I'm reading it now . https://learn.microsoft.com/en-us/sql/t-sql/functions/trigger-nestlevel-transact-sql – Royi Namir Sep 19 '17 at 12:57

1 Answers1

1

Did you try using CONTEXT_INFO()?

ALTER TRIGGER test1
   ON table
   AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    IF NOT UPDATE(x)
        RETURN;

    DECLARE @ci varbinary(128) = CAST('Trigger=' + cast(OBJECT_NAME(@@PROCID) AS varchar(128)) + REPLICATE(' ', 128) as varbinary(128));
    SET CONTEXT_INFO @ci;

    UPDATE table
        SET a = 1
        WHERE id = 1;

    SET CONTEXT_INFO 0x0; -- clear
END
GO

alter TRIGGER test2
   ON table
   AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    IF NOT UPDATE(a)
        RETURN;

    DECLARE @ci varchar(128) = (SELECT CAST(CONTEXT_INFO() AS varchar) FROM master.dbo.SYSPROCESSES WHERE spid = @@SPID);
    DECLARE @log varchar(2048) = CONCAT('CALLER:', @ci);
    EXEC xp_logevent 60000, @log, informational;

    -- update b
END
GO

This give me log message:

Date        19.09.2017 18:31:21
Log     SQL Server (Current - 19.09.2017 18:29:00)

Source      spid74

Message
CALLER:Trigger=test1
Ruslan K.
  • 1,912
  • 1
  • 15
  • 18