Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Access variables values from Trigger

By Tejas Shah,

Introduction

Recently, while working on an application that required a good deal of auditing I came across the following requirement. When a user deletes a record from the application, the database should log the name of the user and the reason for the deletion into an audit table. The application passes these pieces of information into the stored procedure. However, the auditing is done from a trigger that does not have access to the parameters passed into the stored procedure.

Solution

Passing information from a stored procedure that performs DML operations to the triggers is not very easy. One workaround is to make use of the CONTEXT_INFO() function. The stored procedure can put the required information into the CONTEXT_INFO of the current session and the trigger can read it. The following example demonstrates this approach.

We have 2 tables: Products and DeletedProducts. In the DeletedProducts table, we store information about the Products that are deleted from "Products" Table, using a trigger on "Products" table. Here is the schema of both of the tables.

CREATE TABLE Products(
 ProductID INT IDENTITY,
 ProductName VARCHAR(100),
 CreatedBy INT 
) CREATE TABLE DeletedProducts( ProductID INT IDENTITY, ProductName VARCHAR(100), Reason VARCHAR(50), DeletedBy VARCHAR(50), DeletedDate DATETIME DEFAULT(GETDATE())
)

When the user tries to delete a 'product' from the application, the following stored procedure is called. The stored procedure packs the name of the user and reason for deletion into an XML document and then stores it in the CONTEXT_INFO of the current session.

CREATE PROC DeleteProduct(
 @UserID INT,
 @ProductID INT,
 @Reason VARCHAR(50)
)
AS
BEGIN 
DECLARE @xml XML, @varXML VARBINARY(128) SELECT @xml = ( SELECT @UserID AS '@UserID', @Reason AS '@Reason' FOR XML PATH('Values') ) SELECT @varXML = CAST(CAST(@xml AS VARCHAR(MAX)) AS VARBINARY(128)) SET CONTEXT_INFO @varXML DELETE FROM Products WHERE ProductID = @ProductID END

Let me explain the logic.

SET CONTEXT_INFO @varXML 

CONTEXT_INFO can store a VARBINARY(128) value and it will be available throughout the current session. In the above example, the XML document is converted into a VARBINARY(128) value and stored into the CONTEXT_INFO.

The DELETE trigger on the Products table can look into the CONTEXT_INFO of the current session and get hold of the VARBINARY(128) value that the stored procedure stored into it prior to the delete operation. The trigger can then convert it to an XML data type value and extract the username and reason for auditing purpose.

CREATE TRIGGER trg_Products_DEL
 ON dbo.Products
 AFTER DELETE
AS
BEGIN
 SET NOCOUNT ON; 
 DECLARE @varXML VARCHAR(MAX), @xml AS XML
 DECLARE @UserID INT, @Reason VARCHAR(50) 
 SELECT @VarXML = CAST(CONTEXT_INFO() AS VARCHAR(MAX))
 SELECT @XML = CAST(REPLACE(@VarXML,CHAR(0),'') AS XML) 
SELECT @UserID = x.v.value('@UserID[1]','INT'), @Reason = x.v.value('@Reason[1]','VARCHAR(50)') FROM @xml.nodes('/Values') x(v)
INSERT INTO DeletedProducts( ProductName, Reason, DeletedBy, DeletedDate) SELECT ProductName, @Reason, @UserID, GETDATE() FROM deleted END

The DELETE trigger can read the session information as:

SELECT CONTEXT_INFO()
SELECT @VarXML = CAST(CONTEXT_INFO() AS VARCHAR(MAX))

Note that the first character in the VARBINARY value should be removed prior to converting the value returned by CONTEXT_INFO() function to an XML data type value. To facilitate this, the value has to be converted into VARCHAR(MAX) first, the first character is removed and then converted into an XML document.


SELECT @XML = CAST(REPLACE(@VarXML,CHAR(0),'') AS XML)

The following example shows how to use XQuery to extract information from the XML document.

SELECT
 @UserID = x.v.value('@UserID[1]','INT'),
 @Reason = x.v.value('@Reason[1]','VARCHAR(50)')
FROM @xml.nodes('/Values') x(v) 

We can use this information to save to "DeletedProducts" table, in order to keep track of User who deleted the record from application.

CONCLUSION

To make this approach, you need to make sure that all stored procedure that deletes rows from the product table should set the context info correctly prior to the delete operation.

Total article views: 7877 | Views in the last 30 days: 3
 
Related Articles
FORUM

Stored Procedure with varchar(max) as parameter

Stored Procedure with varchar(max) as parameter

ARTICLE

Usages of CONTEXT_INFO

How many of you have ever used SET CONTEXT_INFO in your SQL Server applications? Chances are it is n...

ARTICLE

Dynamic creation of Insert, Update, Delete Stored procedures

Automates the creation of INSERT, UPDATE & DELETE stored procedure of a table

FORUM

cannot generate SSPI context

cannot generate SSPI context

FORUM

Cannot generate SSPI context

Cannot generate SSPI context

Tags
stored procedures    
triggers    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones