SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Tejas Shah / Access variables values from Trigger / Latest Posts

RE: Access variables values from Trigger

aszendi — Fri, 11 Jun 2010 14:19:04 GMT

Hello,I think the example is not the luckiest as once you have a stored proc that deletes the record it could take care of the auditing part as well.Adding auditing to a DB where DMLs commands are not wrapped into storedprocs is more tricky.Áron-Hungary

RE: Access variables values from Trigger

dbishop — Wed, 09 Jun 2010 20:20:11 GMT

In light of the other posts, this is just to clarify a comment you made in your article. You stated, "the first character is removed and then converted into an XML documnet." The subsequent code line was:[code]SELECT @XML = CAST(REPLACE(@VarXML,CHAR(0),'') AS XML)[/code]However, the CHAR(0) does not replace the first character, but defines an ASCII 000 character, sometimes referred to as a NULL (not associated with a SQL NULL value) or end-of-document character.Thus, this line of code is replacing an ASCII 0 with an empty string character (which is probably located at the end of the string, not the first character.

RE: Access variables values from Trigger

forjonathanwilson — Wed, 09 Jun 2010 11:47:03 GMT

[quote][b]john_wong_ca (6/9/2010)[/b][hr]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.:-)[/quote]absolutely right :w00t:a solution that will work for replication is to add username and reason fields to the table, populate them with your delete proc then select them from DELETED in the trigger.on the same topic, heres a cool slice I wrote to figure out what field changed to activate the trigger, in case you dont REALLY want to fire triggers generating history when a certain set of fields is updated, as in the case of the darned replication id... (intended to be inserted into the trigger whose action you want to modify based on the field(s) updated)BEGIN TRANSACTIONdeclare @NameToCheck varchar(8)set @NameToCheck = 'val3' --this is the column name to check for singleton update, set to ModifiedDate, --if we need to check any combination of values, just expand this routine to compensatedeclare @CheckSum int --holds the int value of two raised to (the ordinal position of this column minus one)declare @ChangeSum int --holds the int value of the COLUMNS_UPDATED varbinary----------------------------------------------------------------------------------------------------------------------------this is the guts of it, as you can see it is only really one line--generate the checksumselect @CheckSum = power(2,colorder-1) --colorder index is 1 based, switch to 0 basedfrom syscolumns where id = (select id --this is the column id of the column we want to watch from sysobjects where name = (select object_name(parent_obj) --the name of the table to which this trigger is attached from sysobjects where xtype = 'tr' --the trigger type label and name = object_name(@@PROCID) --the name of this trigger)) AND name = @NameToCheck --is the column we are watching--if more than one field needs checked, just do again and add the check sums together... or do some other clever thing...-----------------------------------------------------------------------------------------------------------------------------recover the change sum, the value of the bit maskset @ChangeSum = cast(COLUMNS_UPDATED()as int)--create meta label for the history recorddeclare @newmeta varchar(200)set @newmeta = '@CheckSum=' + cast(@CheckSum as varchar(8)) + ' 'set @newmeta = @newmeta + '@ChangeSum=' + cast(@ChangeSum as varchar(8))--here we check if our pattern of change occurredif(@CheckSum = @ChangeSum)begin set @newmeta = @newmeta + ' update only in ' + @NameToCheck + ' where we watched' goto createHistoryend--otherwise set @newmeta = @newmeta + ' some other update'createHistory: INSERT INTO jonathan1H ( val1, val2, val3, modDate, meta ) SELECT val1, val2, val3, getdate(), @newmeta FROM deleted COMMIT TRANSACTION

RE: Access variables values from Trigger

UMG Developer — Wed, 09 Jun 2010 10:53:09 GMT

Interesting article, thanks. I'm not sure I will end up using it, but I will keep it in the back of my mind for when an issue might come up.

RE: Access variables values from Trigger

Tejas Shah SQLYoga — Wed, 09 Jun 2010 10:26:14 GMT

Why not just insert the row into the audit table first from SP directly and delete the actual table since you have all the needed info within SP itself? Using the trigger and context_info is complicating the code for no good reason that I can see based on the info in the article.I'm a big fan of KIS (keep it simple) principal. If you can avoid the triggers in the first place, absolutely avoid them.Tejas:In Application, it might happen that we allow the user to delete record from multiple pages OR sometimes some record will be deleted based on business condition. In any case, we need to make sure that we have audit data, who deleted and the reason?To make sure that we have audit data, trigger is a place where we can write a code to keep audit data when any information of that table is being changed.Thanks,TejasSQLYoga.com

RE: Access variables values from Trigger

Tejas Shah SQLYoga — Wed, 09 Jun 2010 10:19:38 GMT

Hi Mahesh,Context_info is unique per session. So, when multiple users are working on product, system will create separate session for each session. So there is no any issue.Thanks,TejasSQLYoga.com

RE: Access variables values from Trigger

david.wright-948385 — Wed, 09 Jun 2010 09:55:47 GMT

[quote][b]minimyme (6/9/2010)[/b]I'm a big fan of KIS (keep it simple) principal. If you can avoid the triggers in the first place, absolutely avoid them.[/quote]Auditing is normally performed in triggers 1) for security purposes so the action is audited however the action is performed and (pretty much) whoever it is performed by and 2) because the auditing logic is decoupled from the deletion logic. Dave.

RE: Access variables values from Trigger

minimyme — Wed, 09 Jun 2010 09:42:25 GMT

RE: Access variables values from Trigger

john.david.birch — Wed, 09 Jun 2010 09:23:14 GMT

providing you keep the number series separate ,and can easily distinguish between them ,and provided loads of people don't login using the same account,and that these are likely to be true next week, month, year,..

RE: Access variables values from Trigger

The Other Chris — Wed, 09 Jun 2010 09:19:04 GMT

[quote][b]Mauve (6/9/2010)[/b][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.[/quote]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).

RE: Access variables values from Trigger

Mauve — Wed, 09 Jun 2010 09:16:03 GMT

[quote][b]sadair (6/9/2010)[/b][hr]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[/quote]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.

RE: Access variables values from Trigger

john.david.birch — Wed, 09 Jun 2010 09:10:41 GMT

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

RE: Access variables values from Trigger

Mauve — Wed, 09 Jun 2010 09:09:14 GMT

[quote][b]christopher.cormier (6/9/2010)[/b][hr]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![/quote]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.

RE: Access variables values from Trigger

sadair — Wed, 09 Jun 2010 09:06:09 GMT

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

RE: Access variables values from Trigger

David Rueter — Wed, 09 Jun 2010 08:36:34 GMT

@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.)

RE: Access variables values from Trigger

Rob Fisk — Wed, 09 Jun 2010 08:18:30 GMT

[quote][b]christopher.cormier (6/9/2010)[/b][hr]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![/quote]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.

RE: Access variables values from Trigger

gulatimahesh — Wed, 09 Jun 2010 07:55:22 GMT

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.

RE: Access variables values from Trigger

The Other Chris — Wed, 09 Jun 2010 07:24:33 GMT

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!

RE: Access variables values from Trigger

john.david.birch — Wed, 09 Jun 2010 07:16:59 GMT

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.[b]Summary[/b]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 ?[b]Procedures for setting the session variable[/b]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 triggersCREATED JDB 9.June.2010---------------------------------------------------------------------------*/ASBEGIN 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[b]Function for retrieving the session variable[/b]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 triggersCREATED JDB 9.June.2010---------------------------------------------------------------------------*/RETURNS INTASbegin 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[b]Code for overriding SqlDataSource[/b]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(); } }}[b]Code for Creating an UPDATE trigger using session data.[/b]-- =============================================-- Author: John David Birch-- Create date: 9.June.2010-- Description: Remember who changed this row-- =============================================CREATE TRIGGER [myUser].[tau_customer] ON [myUser].[ customer] AFTER UPDATEAS BEGIN SET NOCOUNT ON; DECLARE @user_id INT; SELECT @user_id = dbo.usf_GetSessionUser(); UPDATE [myUsert].[customer] SET ChangedBy = @user_id, ChangedDate=GetDate();END

RE: Access variables values from Trigger

Mike Dougherty — Wed, 09 Jun 2010 07:13:38 GMT

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.

RE: Access variables values from Trigger

sknox — Wed, 09 Jun 2010 06:56:59 GMT

[quote][b]simon.ellistonball (6/9/2010)[/b][hr]A nice idea, but your trigger relies on the fact that you would only ever delete one product at a time.[/quote]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.

RE: Access variables values from Trigger

simon.ellistonball — Wed, 09 Jun 2010 05:24:51 GMT

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.

RE: Access variables values from Trigger

honza.mf — Wed, 09 Jun 2010 04:46:06 GMT

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.

RE: Access variables values from Trigger

hardikshah307 — Wed, 09 Jun 2010 04:42:32 GMT

nice article

RE: Access variables values from Trigger

David Rueter — Wed, 09 Jun 2010 04:07:17 GMT

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.

RE: Access variables values from Trigger

david.wright-948385 — Wed, 09 Jun 2010 02:35:27 GMT

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 :-)

RE: Access variables values from Trigger

Fiacre Lenehan — Wed, 09 Jun 2010 02:26:38 GMT

An alternative is to use a temp table created by the proc which will be visible to the trigger

RE: Access variables values from Trigger

john_wong_ca — Wed, 09 Jun 2010 00:14:07 GMT

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.:-)

Access variables values from Trigger

Tejas Shah SQLYoga — Tue, 08 Jun 2010 22:46:28 GMT

Comments posted to this topic are about the item [B]Access variables values from Trigger[/B]