none
T-SQL: Problems to using CONTEXT_INFO to bypass Update Trigger processing

    Question

  • SQL Ver:  2008 R2

    Problem: I having problems getting CONTEXT_INFO to work properly.  I'm trying to run a stored procedure (SP1) which runs an UPDATE statement on records in Table1.  Table 1 has an Update Trigger (trg_Table1_Update).  When I run SP1, I want to bypass the UPDATE Trigger.  In other words, I don't want "trg_Table1_Update" to Execute. 

    The following code template, which uses CONTEXT_INFO, is what I am using and it doesn't seem to be bypassing the trigger execution. 

    Any help would be appreciated.

    RELATED CODE TEMPLATE:

    1. PLACE IN UPDATE TRIGGER AT BEGINNING OF TRIGGER PROCESSING:
      BEGIN  --TRIGGER PROCESSING
      DECLARE @CONTEXT_INFO varbinary(128),@SPID int
      --call system Function to get context info
       SELECT @CONTEXT_INFO = CONTEXT_INFO()
      --Cast the binary data to plain text
       select CAST(@CONTEXT_INFO as varchar(128))
      --Joining with Inserted Table to  update InsertedApplication column
      
      IF CAST(@CONTEXT_INFO as varchar(128)) <> 'usp_EEOTrackingHours' OR @CONTEXT_INFO IS NULL
      BEGIN  -- TRIGGER PROCESSING
       --TRIGGER CODE HERE
      END     --END TRIGGER PROCESSING
      --NOTE:  NO END OR ELSE REQUIRED HERE
      END  --TRIGGER PROCESSING
     
     2. PLACE IN STORED PROCEDURE:
      BEGIN --SP
      
      --SET CONTEXT_INFO VARIABLE TO BY PASS UPDATE TRIGGER
      DECLARE
       @CONTEXT_INFO varbinary(128)
       SELECT @CONTEXT_INFO = cast('usp_EEOTrackingHours' + space(128) as binary(128))
       
       --Set the CONTEXT_INFO with the storedprocedure name
       SET CONTEXT_INFO @CONTEXT_INFO
      
       BEGIN --STORED PROCEDURE CODE


    Bob Sutor

    Friday, December 20, 2013 10:37 PM

Answers

  • You mix varbinary and binary, this is like to cause your problems.

    As it happens, there is a simpler solution without CONTEXT_INFO. (A second problem with context_info is that the value you set with SET CONTEXT_INFO will not be reset when you exit the procedure.)

    In the trigger, put this

       IF object_id('tempdb..#global$bypass') IS NULL
       BEGIN
          -- Do bypassable stuff
       END

    In the procedure you do:

       CREATE TABLE #global$bypass(a int NOT NULL)

    There does not have to be any data in the temp table. Its mer existence serves as a global flag.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by ConstPM Sunday, December 22, 2013 6:10 PM
    Friday, December 20, 2013 11:22 PM

All replies

  • You mix varbinary and binary, this is like to cause your problems.

    As it happens, there is a simpler solution without CONTEXT_INFO. (A second problem with context_info is that the value you set with SET CONTEXT_INFO will not be reset when you exit the procedure.)

    In the trigger, put this

       IF object_id('tempdb..#global$bypass') IS NULL
       BEGIN
          -- Do bypassable stuff
       END

    In the procedure you do:

       CREATE TABLE #global$bypass(a int NOT NULL)

    There does not have to be any data in the temp table. Its mer existence serves as a global flag.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by ConstPM Sunday, December 22, 2013 6:10 PM
    Friday, December 20, 2013 11:22 PM
  • Erland,

    Your code worked perfectly!

    Thanks

    ....bob


    Bob Sutor

    Sunday, December 22, 2013 6:10 PM