[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 446
  • Last Modified:

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
0
CoastalData
Asked:
CoastalData
  • 4
  • 2
  • 2
1 Solution
 
ste5anSenior DeveloperCommented:
What does SUBSTRING return?

You need to convert it explicitly to varbinary.
0
 
CoastalDataAuthor Commented:
I'm not sure how to output it in such a way that I can see it. Suggestions?
0
 
Randy PooleCommented:
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_info

Open in new window

0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
CoastalDataAuthor Commented:
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.
0
 
Randy PooleCommented:
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.
0
 
CoastalDataAuthor Commented:
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
0
 
ste5anSenior DeveloperCommented:
E.g.
DECLARE @context_info VARBINARY(128) = CONTEXT_INFO();

SET @context_info = COALESCE(@context_info, 0xEF, 0xEF + SUBSTRING(@context_info, 2, 127));

SET CONTEXT_INFO @context_info;

Open in new window

0
 
CoastalDataAuthor Commented:
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!
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now