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.