SQL Trigger: Prevent recursive execution not working
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
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
ASKER
I'm not sure how to output it in such a way that I can see it. Suggestions?
Give this a try
DECLARE @context_info varbinary(128)
Select @context_info=CONTEXT_INFO()
IF SUBSTRING(@context_info, 1, 1) = 0xEF
RETURN;
--Although the @context_info of the substring should be something else
SET @context_info = 0xEF + SUBSTRING(@context_info, 2, 127) --set first byte but preserve the rest
SET CONTEXT_INFO @context_infoASKER
Nope, that gives me the same error:
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.
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.
post the new code your using, you did change @context_info in 0xEF + SUBSTRING(@context_info, 2, 127) to something that has a value? Most likely you are trying to assign a null value.
ASKER
I'm using the exact code modification that you posted:
DECLARE @context_info varbinary(128)
Select @context_info=CONTEXT_INFO
IF SUBSTRING(@context_info, 1, 1) = 0xEF
RETURN;
--Although the @context_info of the substring should be something else
SET @context_info = 0xEF + SUBSTRING(@context_info, 2, 127) --set first byte but preserve the rest
SET CONTEXT_INFO @context_info
DECLARE @context_info varbinary(128)
Select @context_info=CONTEXT_INFO
IF SUBSTRING(@context_info, 1, 1) = 0xEF
RETURN;
--Although the @context_info of the substring should be something else
SET @context_info = 0xEF + SUBSTRING(@context_info, 2, 127) --set first byte but preserve the rest
SET CONTEXT_INFO @context_info
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Bam! There you go Ste5an, you got it! I guess that means I should read up on COALESCE, but that'll have to wait until later... for now, I'm just happy to have this trigger working!
Thanks!
Thanks!
You need to convert it explicitly to varbinary.