June 8, 2010 at 10:46 pm
Comments posted to this topic are about the item Access variables values from Trigger
Tejas Shah
June 9, 2010 at 12:14 am
That's a good idea. This will work for most of cases but not for replication. If a table is published in a merge publication, while replication agent is modifying the data, the connection of the agent will use context info as part of the identification of merge agent. In this case, the context info passed in the trigger will not be xml conversable.:-)
June 9, 2010 at 2:26 am
An alternative is to use a temp table created by the proc which will be visible to the trigger
June 9, 2010 at 2:35 am
A very good article to show how context_info works and how it can be applied.
If the 128 bytes of context_info is insufficient for the purpose, a CLR could be used to first register persistent, session related context information, then retrieve it.
I'm not so sure the auditing function should rely on cooperation from the process that is deleting the rows though 🙂
June 9, 2010 at 4:07 am
Nice article, and interesting use of CONTEXT_INFO.
Be careful that nothing else depends on CONTEXT_INFO though! (Overwriting CONTEXT_INFO could have unintended impact on other routines that rely on session information stored there.)
While not suitable for deletions or for minimally-invasive auditing, another approach to passing in data to a trigger is to use an INSTEAD OF trigger on a view:
Create a view that returns all the table columns, plus adds an additional "parameter" column or columns.
Then you can create an INSTEAD OF INSERT trigger (or INSTEAD OF UPDATE) on the view.
This allows you to perform the insert into the view (instead of the underlying table), providing values for the "parameter" column--and the trigger has the ability to read the "parameter" column.
(To clarify, the "parameter" column doesn't really get stored anywhere or returned by the view: it is in the view solely to allow a parameter value to be passed in on updates or inserts.)
This approach assumes SQL 2005, but provides the benefit of efficiently passing in parameters for each row (as opposed to per-execution), and allows the parameter(s) to be of any data type.
This approach doesn't do any good for deletes, and is probably not ideal for minimally-invasive passive auditing...but I've used it extensively with great success.
June 9, 2010 at 4:42 am
nice article
June 9, 2010 at 4:46 am
Nice idea. But we have context info already used for other data. It is possible to this data into a variable, use this trick and restore them. I'm afraid of some subprocess that will want the original.
June 9, 2010 at 5:24 am
A nice idea, but your trigger relies on the fact that you would only ever delete one product at a time. You'd have to be very careful about other procedures which perform deletes and may not fill the context_info.
A global temporary table, or even a persistent table with a key based on the connection id, or better the transaction id from dm_tran_current_transaction would be better, and allow you to delete multiple rows.
June 9, 2010 at 6:57 am
simon.ellistonball (6/9/2010)
A nice idea, but your trigger relies on the fact that you would only ever delete one product at a time.
No, it doesn't. It does rely on having only one UserID and Reason for a given batch of deletes, and it does rely on context_info being XML, but the trigger as written should properly audit a deletion of multiple records in a batch if the previous conditions are met.
June 9, 2010 at 7:13 am
I can appreciate a get-it-done-today workaround, but wouldn't a better approach be to rewrite the trigger/application layers to provide better tier isolation? I assumed a trigger should be encapsulated such that it can provide integrity validation of data based solely on RI rules; it should not need information provided by the user in the SP scope. Likewise the business logic probably should be enforced at the interface between data and the application (aka SP). Pushing this requirement all the way back to the UI, the application should not even call for a delete without proper authentication and "reason" coding.
I also wonder what kind of performance you can expect from the xml->CONTEXT_INFO->xml happening per transaction. Granted deletes may be infrequent but transactional overhead should be considered before it becomes a performance problem.
I guess I don't understand why the auditing is done in the trigger rather than directly in the SP. I have seen where the trigger provides a single place to hook actions on the data when multiple SP already exist that act on the table. This solution would still require visiting each SP to ensure the context_info is properly set.
Thanks for the article. I don't think I would have learned about the context_info myself.
June 9, 2010 at 7:16 am
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](
@user-id 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 @user-id 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 @user-id INT;
SELECT @VarXML = CAST(CONTEXT_INFO() AS VARCHAR(MAX))
SELECT @XML = CAST(REPLACE(@VarXML,CHAR(0),'') AS XML)
SELECT @user-id = 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
June 9, 2010 at 7:24 am
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!
June 9, 2010 at 7:55 am
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.
June 9, 2010 at 8:18 am
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.
[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]
June 9, 2010 at 8:36 am
@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.)
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply