We help IT Professionals succeed at work.
Get Started

SQL Trigger: Prevent recursive execution not working

Jon Jaques
Jon Jaques asked
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

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!

Watch Question
Senior Developer
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