A trigger takes no arguments but it is possible to use CONTEXT_INFO() to make 128 bytes of binary data values accessible to it. I wanted to provide an 8-byte (bigint) value from a sequence which the trigger would write to a primary key column.

Storing and retrieving a numeric value in CONTEXT_INFO is not difficult, but it’s another thing that doesn’t come up very often. For this type of problem I like to find concise explanations with source code so that I can get back to the main problem quickly. Once again my searches didn’t turn anything up so I’m trying to fill the gap with this post.

One query is worth a thousand words (as somebody didn’t say). Here are four Transact-Sql examples – see if you agree (you’ll have to run them individually):

/*
OK when data type converted to matching number of bytes on CONTEXT_INFO write/read
*/
DECLARE @ValueBin varbinary(128) = CAST(CAST(123456 as int) as varbinary(4))
SET CONTEXT_INFO @ValueBin
SELECT CONVERT(int, CONVERT(varbinary(4), CONTEXT_INFO()))

/*
OK when data type converted to larger number of bytes on write
*/
DECLARE @ValueBin varbinary(128) = CAST(CAST(123456 as int) as varbinary(128))
SET CONTEXT_INFO @ValueBin
SELECT CONVERT(int, CONVERT(varbinary(4), CONTEXT_INFO()))

/*
Fails on read when binary value larger than receiving data type
*/
DECLARE @ValueBin varbinary(128) = CAST(CAST(123456 as int) as varbinary(4))
SET CONTEXT_INFO @ValueBin
SELECT CONVERT(int, CONVERT(varbinary(8), CONTEXT_INFO()))

/*
Fails on read when binary value and receiving data type match each other but not the original data type written
*/
DECLARE @ValueBin varbinary(128) = CAST(CAST(123456 as int) as varbinary(4))
SET CONTEXT_INFO @ValueBin
SELECT CONVERT(bigint, CONVERT(varbinary(8), CONTEXT_INFO()))

So the key point seems to be ensuring that when the value is read, the intermediate binary conversion and the final data type need match the length in bytes of the original value. Problems can be insidious as you’ll often receive a 0 or completely spurious number instead of an error.