Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Access variables values from Trigger Expand / Collapse
Author
Message
Posted Wednesday, June 9, 2010 7:16 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 8, 2012 1:46 AM
Points: 148, Visits: 31
I liked this so much I had to apply it to the product I am working on straight away. Below is how I managed a quick solution to ASP.NET/C# T-SQL integration using SQlDataSource and triggers.

Summary
This document describes how changes to data can be logged without the need to transfer “Updated_by” info via update/insert statements and provides a mechanism for logging deleted data too.

In this example the log data is stored in the data table but this need not to be the case.

The mechanism involves transferring Session Data from the ASP.NET web server to the Session Data (in the form of CONTEXT INFO) in the database. The session data remains alive from the OPEN CONNECTION call to the CLOSE CONNECTION. This means that UPDATE triggers can use the database session data to establish “Updated by”.

By using Triggers to log the changes, it is no longer necessary to pass the “Changed Date” data from the application to the database. The database trigger knows what the date is and can make use of the GetDate() function.

A procedure is created for storing SessionData and a function is created for retrieving SessionData.

While the C# code In the DB.NewCon() function can be adapted to set the session data immediately after the OPEN call to deal with connections established using “Code Behind”, a new class inherited from “SqlDataSource” is used to perform a similar action prior to actual updates etc. Other technologies may be able to implement similar solutions. LINQ ?



Procedures for setting the session variable

CREATE PROC [dbo].[usp_SetSessionUser](
@UserID INT
)
/*---------------------------------------------------------------------------DESCRIPTION Remember the User_id in the session's context_info so that it can be recalled later, e.g. in triggers
CREATED JDB 9.June.2010
---------------------------------------------------------------------------*/
AS
BEGIN
DECLARE @xml XML, @varXML VARBINARY(128)
SELECT @xml = ( SELECT @UserID AS '@UserID' FOR XML PATH('Values') ) ;
SELECT @varXML = CAST(CAST(@xml AS VARCHAR(MAX)) AS VARBINARY(128)) ;
SET CONTEXT_INFO @varXML
END

Function for retrieving the session variable
CREATE FUNCTION [dbo].[usf_GetSessionUser]()
/*---------------------------------------------------------------------------DESCRIPTION Remember the User_id in the session's context_info so that it can be recalled later, e.g. in triggers
CREATED JDB 9.June.2010
---------------------------------------------------------------------------*/
RETURNS INT
AS
begin

DECLARE @varXML VARCHAR(MAX), @xml AS XML;
DECLARE @UserID INT;

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')
FROM @xml.nodes('/Values') x(v);
RETURN(@UserID);
END





Code for overriding SqlDataSource

namespace SQL
{
public class MySqlDataSource : SqlDataSource
{
/* This override of the "SqlDataSource" class provides Transferrence of the the ASP.NET Session variable "user_id" to
* the database's Session variable "Context_info" so that it can be accessed in UPDATE triggers.
* Update and Delete should be tackled similarly. */
public MySqlDataSource()
{
base.Updating += new SqlDataSourceCommandEventHandler(Updating2);
}
public void Updating2(Object source, SqlDataSourceCommandEventArgs e)
{
DbCommand command = e.Command;
SqlConnection con = (SqlConnection)command.Connection;
con.Open();

string sql = @"EXEC dbo.usp_SetSessionUser " + Page.Session["user_id"].ToString();
SqlCommand command2 = new SqlCommand(sql, con);
command2.ExecuteNonQuery();
}
}
}
Code for Creating an UPDATE trigger using session data.


-- =============================================
-- Author: John David Birch
-- Create date: 9.June.2010
-- Description: Remember who changed this row
-- =============================================
CREATE TRIGGER [myUser].[tau_customer]
ON [myUser].[ customer]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @user_id INT;
SELECT @user_id = dbo.usf_GetSessionUser();
UPDATE [myUsert].[customer] SET ChangedBy = @user_id, ChangedDate=GetDate();
END
Post #934591
Posted Wednesday, June 9, 2010 7:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 9, 2013 1:35 PM
Points: 2, Visits: 25
This may be a faily easy quesion, but aside from the business requirement are there any performance benefits of putting the audit trail in a trigger? I'm fairly new to SQL, but if the only way for users to delete a record is to use the mentioned stored procedure I think I would leave the audit trail piece in the procedure itself.

Are there any performance/security benefits of using triggers for audit trails?

Thanks!
Post #934595
Posted Wednesday, June 9, 2010 7:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 20, 2013 3:29 AM
Points: 4, Visits: 14
Its a nice artical but will it work in multiuser case? If same users deletes the product at the same time? I don't think so as in that case ContextInfo will shore which value & will be a conflict.. Pls suggest.
Post #934605
Posted Wednesday, June 9, 2010 8:18 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 22, 2014 8:32 AM
Points: 163, Visits: 428
christopher.cormier (6/9/2010)
This may be a faily easy quesion, but aside from the business requirement are there any performance benefits of putting the audit trail in a trigger? I'm fairly new to SQL, but if the only way for users to delete a record is to use the mentioned stored procedure I think I would leave the audit trail piece in the procedure itself.

Are there any performance/security benefits of using triggers for audit trails?

Thanks!


One reason for having it in a trigger is to cover instances where the SP is bypassed.
You could use an instead of so that if the info was missing then the deletion did not take place.

Alternatively you could record suser_sname() if the user info was missing though of a web app this woudl be less useful.


_______________________________________________________
Change is inevitable... Except from a vending machine.
Post #934622
Posted Wednesday, June 9, 2010 8:36 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 29, 2014 6:54 AM
Points: 53, Visits: 417
@gulatimahesh: It depends on how the application manages SQL connections. CONTEXT_INFO is unique per connection.

If the application establishes separate SQL connections for each application instance, CONTEXT_INFO will be fine in a multi-user environment.

If connection pooling is involved, obviously by definition SQL connections could be shared between multiple instances...and in this case it would be important to understand exactly how/when the pooling assigned connections.

Also, with connection pooling, there have been some reports that sp_reset_connection does not clear CONTEXT_INFO...though some suggest that was fixed in SQL 2005. (In other words, be careful that when you obtain a connection from the pool that you don't accidently use CONTEXT_INFO that was set by a previous connection.)
Post #934638
Posted Wednesday, June 9, 2010 9:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 2:24 PM
Points: 29, Visits: 58
Great article, thank you.
This will all work well when the delete is accomplished via the proc, but what happens when the delete is ad hoc from Enterprise Manager or other? Will the trigger fail or will it pull data from an existing CONTEXT_INFO and put incorrect values in the audit table?
Just my thoughts,
Steve



Post #934672
Posted Wednesday, June 9, 2010 9:09 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 28, 2014 6:38 AM
Points: 1,182, Visits: 1,972
christopher.cormier (6/9/2010)
This may be a faily easy quesion, but aside from the business requirement are there any performance benefits of putting the audit trail in a trigger? I'm fairly new to SQL, but if the only way for users to delete a record is to use the mentioned stored procedure I think I would leave the audit trail piece in the procedure itself.

Are there any performance/security benefits of using triggers for audit trails?

Thanks!

We use the exact same method for auditing of deletions. Triggers are used to audit the row deletions of related (i.e., child) tables via a Foreign Key (FK) cascade delete. A very common situation. Especially in a hierarchical tree structure.



(PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.
Post #934674
Posted Wednesday, June 9, 2010 9:10 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 8, 2012 1:46 AM
Points: 148, Visits: 31
well if the Context_info is not set then NULL will be entered into the audit, which might be a giveaway that the DBA ADMIN has been messing with prod data provided triggers are used.

On th eother hand if the proc is doing all the auditing directly then a nulle could mean anything
Post #934676
Posted Wednesday, June 9, 2010 9:16 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 28, 2014 6:38 AM
Points: 1,182, Visits: 1,972
sadair (6/9/2010)
Great article, thank you.
This will all work well when the delete is accomplished via the proc, but what happens when the delete is ad hoc from Enterprise Manager or other? Will the trigger fail or will it pull data from an existing CONTEXT_INFO and put incorrect values in the audit table?
Just my thoughts,
Steve

Depending upon how the code in the trigger is written as it relates to CONTEXT_INFO, it will either fail or succeed. In our situation, we have code in the trigger to validate that the value in CONTEXT_INFO is correct.

As for ad hoc row deletions via SSMS, we don't do it against our production systems. When the situation is really needed, we use a T-SQL script that properly sets CONTEXT_INFO before calling the stored procedure or performs the actual row deletion. The audit trail is then correct as the information will record that someone other than a customer user performed the deletion.



(PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.
Post #934681
Posted Wednesday, June 9, 2010 9:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 9, 2013 1:35 PM
Points: 2, Visits: 25
Mauve (6/9/2010)[hrWe use the exact same method for auditing of deletions. Triggers are used to audit the row deletions of related (i.e., child) tables via a Foreign Key (FK) cascade delete. A very common situation. Especially in a hierarchical tree structure.


So I guess in an ideal situation there may be several layers of auditing both from the main table and linked tables. Would it make sense to add a conditional to the trigger such that if nothing was added in the CONTEXT_INFO you would grab SYSTEM_USER? That way if the information was blank from running a query directly you would still be able to trace down who deleted the record(s).
Post #934686
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse