April 23, 2012 at 10:40 am
All,
I am trying to write a quick audit tracker to see where a certain value is changing.
I know I can do this inline via code like this utilizing OUTPUT:
UPDATE dbo.MyTable
SET CreationUser = 'User2'
OUTPUT
ISNULL( OBJECT_NAME(@@PROCID), 'Dynamic SQL/Query Analyzer' ),
deleted.Field1, inserted.Field1,
deleted.Field1, inserted.Field2,
...
deleted.CreationDateTime, inserted.CreationDateTime,
deleted.CreationUser, inserted.CreationUser,
deleted.LastUpdateDateTime, inserted.LastUpdateDateTime,
deleted.LastUpdateUser, inserted.LastUpdateUser,
GETDATE(), SUSER_SNAME()
INTO dbo.TableAudit
WHERE Field1 = 35
is there an extensible way to do the same thing via trigger? I'm thinking that if I write it as a trigger, I can log exactly when the field changes, as opposed to above which will log all updates whether or not the SPECIFIC field changes.
The key things I need is that Field1 was changed, and which Stored Procedure it occurred in.
Thoughts? This is in 2008R2,
Thanks,
Doug
April 23, 2012 at 11:07 am
Triggers don't have access to data in that scope. They just know what changed, not how.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 23, 2012 at 12:09 pm
As stated, you cannot use @@PROCID to see the calling proc. As you have no doubt found out @@PROCID returns the trigger's identity.
You could however look at @@SPID and refer back to the DMVs to figure what SQL was used to begin the process. Isolating the called proc be hard and expensive to do and would only tell you the outer proc call...meaning you may not always be able to find the name of the proc most local to the change.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 23, 2012 at 12:14 pm
You can use SET CONTEXT_INFO and CONTEXT_INFO(), but it requires changing every single procedure
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 23, 2012 at 12:19 pm
GilaMonster (4/23/2012)
You can use SET CONTEXT_INFO and CONTEXT_INFO(), but it requires changing every single procedure
And will skip if someone does an ad hoc Update script, or if any of a dozen other things interfere with setting it correctly.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 23, 2012 at 12:22 pm
GSquared (4/23/2012)
GilaMonster (4/23/2012)
You can use SET CONTEXT_INFO and CONTEXT_INFO(), but it requires changing every single procedureAnd will skip if someone does an ad hoc Update script, or if any of a dozen other things interfere with setting it correctly.
Yeah. It's one of those 'It works except where it doesn't' type solutions.
Doug, you're on SQL 2008, have you investigated SQLAudit?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 23, 2012 at 12:24 pm
On the point of knowing if a certain column is updated, you can use an Output Into clause to check before/after on a column pretty easily. That keeps it in the proc's scope, which makes coding in the proc name pretty easy.
Declare a table variable, or create a temp table, use Output Into to put data into it from INSERTED and DELETED. Then the table variable will have before/after values in it, and you can compare those, and insert into a log table if it actually changes.
This also avoids the issue where Update() will report a column as being changed if, for example, you change a value to the same value. It can also capture which rows were updated if that's got potential variability to it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply