SQL Trigger: Prevent recursive execution not working
Posted on 2014-08-20
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
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!