CONTEXT_INFO for audit data?


Author
Message
Keith Chisarik
Keith Chisarik
StrataFrame VIP (2.4K reputation)StrataFrame VIP (2.4K reputation)StrataFrame VIP (2.4K reputation)StrataFrame VIP (2.4K reputation)StrataFrame VIP (2.4K reputation)StrataFrame VIP (2.4K reputation)StrataFrame VIP (2.4K reputation)StrataFrame VIP (2.4K reputation)StrataFrame VIP (2.4K reputation)
Group: StrataFrame Users
Posts: 939, Visits: 40K
Does anyone have an opinion on using CONTEXT_INFO as an efficient way to "pass" application information to auditing triggers? Once could update the CONTEXT_INFO for the current connection string before doing any SAVE() to a table that required auditing. This would allow your triggers to include data from your application that is not necessarily in the table you are updating, RBS username for example (I cannot just use the SQL username). I need to include several fields in each audit record, the data will never be contained in the table being audited. CONTEXT_INFO accepts 128 bytes of binary data (fast so far in testing). I was theorizing to pass a string of the values, delimited in some way that I could parse via the TSQL trigger code. One stop to set my "custom" audit fields whenever they change before a save or transaction on the BO gets committed?

Thoughts? or suggestions on what you may have done to pass data to your audit triggers?

Keith Chisarik

Reply
Greg McGuffey
Greg McGuffey
Strategic Support Team Member (4.8K reputation)
Group: Forum Members
Posts: 2K, Visits: 6.6K
I didn't even know about CONTEXT_INFO! Depending on what you are doing, you might look into CLR triggers, as the string manipulation would be much easier (er...any kind of parsing would be much easier). The major limitation I see with it is that it is only 128 bytes of info. If that works, great. My users want comments though. Sad



In my auditing design, I have two tables, one is the master audit history table, containing the table, ID, User name, date, type and comment of any change. The other table stores the old and new values of any changed fields. I use three sprocs to manage this. One is the master sproc that saves an audit record about any change, i.e. table being changed, ID of changed row, user name, date and a comment about the change. Then I have a sproc that saves away the old values and one that saves the new values. I use CodeSmith to manage the last two (it builds the sprocs for me, so when I change a table, I don't have to manually go update the old/new value sprocs). Then I have a base BO that manages the extra data needed for auditing and manages calling the sprocs appropriately during save/update/delete.



Let us know how using the CONTEXT_INFO goes. I'm going to look into this more myself.
GO

Merge Selected

Merge into selected topic...



Merge into merge target...



Merge into a specific topic ID...





Similar Topics

Reading This Topic

Login

Explore
Messages
Mentions
Search