We help IT Professionals succeed at work.
Get Started

SQL Trigger: Prevent recursive execution not working

Jon Jaques
Jon Jaques asked
on
513 Views
Last Modified: 2014-08-21
Hello, I've been trying to work out a trigger for my database, but am still having a problem with a bit of contributed code which prevents the trigger from being executed in a recursive fashion.

The code uses CONTEXT_INFO(), and looks like this:

-- Check to make sure this is not being executed recursively
IF SUBSTRING(CONTEXT_INFO(), 1, 1) = 0xEF
    RETURN;

DECLARE @context_info varbinary(128)
SET @context_info = 0xEF + SUBSTRING(CONTEXT_INFO(), 2, 127) --set first byte but preserve the rest
SET CONTEXT_INFO @context_info

But when I try to insert a record into the database, I get the following error, even though as near as I can tell it is getting the information it needs:

Msg 2743, Level 16, State 3, Procedure tblTripClient__TRG_INS, Line 31
SET CONTEXT_INFO option requires varbinary (128) NOT NULL parameter.
The statement has been terminated.

Can anybody help? Thanks in advance!

--Jon
Comment
Watch Question
Senior Developer
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 8 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE