Access Variable Inside a Trigger

Have you ever needed to pass a variable into a trigger? So far, I have not encountered the need to pass a variable to a trigger. I figure, sooner or later, I am going to encounter a similar requirement. And while I can still remember this simple workaround, might as well post it.

The idea is to take advantage of the CONTEXT_INFO statement. CONTEXT_INFO is a statement that returns the context_info value that was set for the current session or batch.

Here’s the remarks excerpt from the BOL:

Multiple active result sets (MARS) enables applications to run multiple batches, or requests, at the same time on the same connection. When one of the batches on a MARS connection runs SET CONTEXT_INFO, the new context value is returned by the CONTEXT_INFO function when it is run in the same batch as the SET statement. The new value is not returned by the CONTEXT_INFO function run in one or more of the other batches on the connection, unless they started after the batch that ran the SET statement completed. 

To illustrate, consider this table:

create table students
(
 StudentId tinyint identity(1,1),
 StudentName varchar(25)
)
insert into students (studentname)
select 'John'
union all
select 'Paul'
union all 
select 'George'

select * from students

This is how the table looks like:

StudentId StudentName
--------- -------------------------
1         John
2         Paul
3         George

Now let's create the trigger..

create trigger trgDelete
on students for delete
as 
begin
 insert into students (studentname)
 values (cast(CONTEXT_INFO() as varchar(10)))
end

Here's the sample code to illustrate...

declare @namevar varchar(10)
DECLARE @BinVar varbinary(128)

set @namevar = 'Ringo'
SET @BinVar = CAST(@namevar AS varbinary(128) )
SET CONTEXT_INFO @BinVar

delete from students 
where studentname = 'Paul'

select * from students

Here is the result after the delete statement...

StudentId StudentName
--------- -------------------------
1         John
4         Ringo     
3         George

This is just a sample that shows how a string can be access inside a trigger. Other data type may also be stored, just don't forget that the value needs to be converted into a varbinary, then just convert it back as needed.
As always, comments are welcome...


~~ CK

What's the Difference Between SET and SELECT?

Is there really a difference between SET and SELECT when used to assign values to variables, in T-SQL? The misconception is that, there's no difference between them. I've been creating SQL Scripts for some time now and had been using these two statements interchangeably. That is until I got an unnoticed logical error resulting from using one instead of the other. So what are the similarities and differences? Both commands can assign values to a variable. For the most part, their similarities end there. Fundamentally, SET is the ANSI standard way of assigning values to a variable, while SELECT is not. Another visible difference is that SET can assign a value to only one variable, while SELECT can assign value to one or more variable at a time. Consider these variables:

declare @myVar1 int, @myVar2 int

This code should run successfully:

SELECT @myVar1 = 1, @myVar2 = 2
print '@myVar1'
print @myVar1
print ''
print '@myVar2'
print @myVar2

Here are the results:

@myVar1
1
 
@myVar2
2

This code will result to an error:

SET @myVar1 = 11, @myVar2 = 12
print '@myVar1'
print @myVar1
print ''
print '@myVar2'
print @myVar2

Here's the error returned:

Msg 102, Level 15, State 1, Line 11
Incorrect syntax near ','.

(I'll use the PRINT command to display variable content to avoid confusion between the command assigning values to variable and displaying values.) In order for this code to run, assignment of values should be done separately for each variable.

SET @myVar1 = 11
SET @myVar2 = 12
print '@myVar1'
print @myVar1
print ''
print '@myVar2'
print @myVar2

Here's the result:

@myVar1
11

@myVar2
12

Standards, however, is not the only consideration when using these two statements. Here's a sample application of how these commands could tremendously affect SQL Scripts:

declare @rowsaffected int, @errornumber int

select id/0 from sysobjects
set @rowsaffected = @@rowcount
set @errornumber = @@error

print 'rowsaffected'
print @rowsaffected
print ''
print 'errornumber'
print @errornumber

Here are the results:

Msg 8134, Level 16, State 1, Line 4
Divide by zero error encountered.

rowsaffected
0

errornumber
0

The code returned an error (division by zero), but the system variable @@error returned zero (0). This is not due to SET and SELECT per se. The @@error system variable checks if there's an error on the last t-sql statement executed. In this case set @rowsaffected = @@rowcount, is a valid statement, hence it did not return any error number. This could result in unhandled errors. In another application, I want to return the number of rows affected.

declare @rowsaffected int, @errornumber int

select id from sysobjects where id <= 10
set @errornumber = @@error
set @rowsaffected = @@rowcount

print 'rowsaffected'
print @rowsaffected
print ''
print 'errornumber'  
print @errornumber

Here are the results:

id
-----------
4
5
7
8

rowsaffected
1
 
errornumber
0

Notice that the query actually returned four rows, but the @@rowcount system variable reported only one row. The @@rowcount is a system variable that returns the number of rows affected by the previous statement. In this case, set @errornumber = @@error, returns one row. Using SET to assign the values of these system variables, in this case, is not advisable. To capture both values at the same time, use the SELECT statement.

declare @rowsaffected int, @errornumber int

select id from sysobjects where id <= 10
select @errornumber = @@error,@rowsaffected = @@rowcount

print 'rowsaffected'
print @rowsaffected
print ''
print 'errornumber'  
print @errornumber

id
-----------
4
5
7
8

rowsaffected
4
 
errornumber
0

select id/0 from sysobjects where id <= 10
select @errornumber = @@error,@rowsaffected = @@rowcount

print 'rowsaffected'
print @rowsaffected
print ''
print 'errornumber'  
print @errornumber
 
-----------
Msg 8134, Level 16, State 1, Line 4
Divide by zero error encountered.

rowsaffected
0
 
errornumber
8134

In both cases, the @@rowcount and @@error returned the right values. SET and SELECT also varies on the way scalar values are being assigned to variables, specifically if the value is coming from a query. Consider this sample table:

SET NOCOUNT ON
declare @Test table (i int, j varchar(15))
INSERT INTO @Test (i, j) VALUES (1, 'First Row')
INSERT INTO @Test (i, j) VALUES (2, 'Second Row')

select * from @Test

i           j
----------- ----------
1           First Row
2           Second Row

Using the two commands:

declare @myVarUseSET varchar(15), @myVarUseSELECT varchar(15)

SET @myVarUseSET = (select j from @Test where i = 2)
SELECT @myVarUseSELECT = j from @Test where i = 2

print '@myVarUseSET'
print @myVarUseSET
print ''
print '@myVarUseSELECT'
print @myVarUseSELECT

Here are the results:

@myVarUseSET
Second Row

@myVarUseSELECT
Second Row

The values are accurately assigned to both variables. What will happen if the query returned more than one rows.

SET @myVarUseSET = (select j from @Test)
SELECT @myVarUseSELECT = j from @Test

print '@myVarUseSET'
print @myVarUseSET
print ''
print '@myVarUseSELECT'
print @myVarUseSELECT

Here are the results:

Msg 512, Level 16, State 1, Line 10
Subquery returned more than 1 value. 
This is not permitted when the subquery follows =, !=, <, <= , >, >= or 
when the subquery is used as an expression.

@myVarUseSET

@myVarUseSELECT
Second Row

Notice that the SET query failed while the SELECT succeeded. How do SELECT statements choose which value among the rows returned will be stored to the variable? It will be the last row returned. If the query has an ORDER BY clause, it will return the last row as sorted by the ORDER BY clause. What will happen if, instead of multiple values returned, the query did not return any rows? To be able to appreciate more, I initialize the value of the variables and tried to assign a value that's coming from a query that did not return any rows.

select @myVarUseSET = 'initial value',
@myVarUseSELECT = 'initial value'

set @myVarUseSET = (select j from @Test where i = 3)
select @myVarUseSELECT = j from @Test where i = 3

print '@myVarUseSET'
print isnull(@myVarUseSET,'NULL')
print ''
print '@myVarUseSELECT'
print isnull(@myVarUseSELECT,'NULL')

Here are the results:

@myVarUseSET
NULL

@myVarUseSELECT
initial value

The way SET executes is that it will set the value of the variable to NULL. SELECT, on the other hand, will not replace the value of the variable. Notice that the variable @myVarUseSELECT still contains the initialized value of the variable. If not properly handled, this could lead to unnoticed errors that might affect the result of any scripts. Lastly, speed. Dealing with a single variable, there's not much of speed differential between the two commands. SET @variable = value and SELECT @variable = value have almost the same speed with very negligible difference. However, if it's dealing with three or more variables SELECT beats SET by almost 50% for the mere fact that SELECT can assign values to multiple variables at a time. Based on the abovementioned samples and cases, speed should not the only thing that must be considered. Keep in mind how these commands handle the results of the assignment operator.


~~ CK