Sql server context_info trigger

What is Trigger ?

A trigger is a database object that is attached to a table or we can define trigger as a stored procedure that automatically executes when an event occurs in the database server.

Triggers are classified into two types mainly-

• Instead of Trigger
• After Trigger

How can i access the tables in Triggers

When we update a record on the table where trigger is created, the magic tables “INSERTED” and “DELETED” both will be created, the Old data of the updating record will be available in “DELETED” table and, the new data will be available in “INSERTED” table, while accessing them inside the trigger. These tables are called magic tables of the SQL Server. We can not see these tables in the data base. But we can access these tables from the trigger.

How can i pass the value to Trigger ?

So now we are coming to the point where i need to send the data to trigger. So in case tables “INSERTED” and “DELETED” does not hold the data, and you still need to pass the data from either from parameters of stored procedure or from some variables which are evaluated in stored procedure then you are in right place :-)

Here i am creating the Test tables which are required for the demo. Below is the screenshot of the tables which are added newly in my “Testing” Database.

Now the intension would be to insert a record in table “DataCheck” when some record is inserted in to table “Test“. So that means i am creating a trigger on table “Test” to insert the record in table “DataCheck“.

As you can see table “Test” has columns – ID, NAME
and table “DataCheck” has columns – UserID and Name.

So now lets create a trigger on table “Test“ -

CREATE TRIGGER USP_TR_Test ON dbo.Test
FOR INSERT
AS
BEGIN
 SET NOCOUNT ON;

 DECLARE 
  @commaseparatedStr VARCHAR(200),
  @useridStr VARCHAR(200),
  @nameStr VARCHAR(200)


 SELECT @commaseparatedStr = CAST(CONTEXT_INFO() AS VARCHAR)
 FROM master.dbo.SYSPROCESSES
 WHERE SPID = @@SPID

 SELECT 
  @useridStr = substring(@commaseparatedStr,1,CHARINDEX(',',@commaseparatedStr)- 1),
  @nameStr = substring(@commaseparatedStr,CHARINDEX(',',@commaseparatedStr) + 1, len(@commaseparatedStr) - 1)


 INSERT INTO DataCheck (
  NAME
  ,UserID
 )
 SELECT 
  SUBSTRING(@namestr,CHARINDEX('=',@namestr) + 1, len(@namestr) - 1),
  CAST(SUBSTRING(@useridstr,CHARINDEX('=',@useridstr) + 1, len(@useridstr) - 1) AS INT)
END
GO

So basically it inserts data into table “DataCheck“.

Now lets create a stored procedure which inserts data into table “Test” and calls up trigger on the same table.

CREATE PROCEDURE USP_RP_InsertToTest 
(
 @intUserID INT, 
 @name VARCHAR(50)
)
AS
BEGIN
 SET NOCOUNT ON;

 DECLARE @CONTEXT_INFO VARBINARY(128)

 SET @CONTEXT_INFO = CAST('UserID=' + CONVERT(VARCHAR(10), @intUserID) + ',' + 'Name =' + @name AS VARBINARY(128))
 SET CONTEXT_INFO @CONTEXT_INFO

 INSERT INTO Test (NAME)
 VALUES (@name)
END
GO

Here we are using keyword called “CONTEXT_INFO” so this is used to retrieve the context information for the current session and session context information is also stored in the context_info columns.

So “SET CONTEXT_INFO variablename” is used to set the context info.

In the trigger if you observe i am using “CONTEXT_INFO()” to fetch the data from context info and insert into “DataCheck“.

Note that the data i am setting to context info is coming as parameters in stored procedures.

Now you just execute the stored procedure created above like this –

and observe that in table “DataCheck” one record is inserted -

Hope it helped.