March 5, 2011 at 3:04 am
Dear All!
I need to audit the table1 records when its gets update ,
Ex: Table1 as table Name and having more than 100 columns
but My task is to store the data existing value and new updated value,
But, I have used trigger for update and record the entire row from DELETED table,
But I want to record the particular column value only which columns is updated ,
Please lete me know to record the exact column values to store the previous and current values
not like entire row
March 5, 2011 at 3:39 am
You could use UNPIVOT to transform the columns into rows for the new and updated rows and select only those where the values don't match.
If this would have too much of an performance impact, you could store the old a new record using the trigger and perform the selection using a scheduled job.
March 5, 2011 at 10:46 am
Use the IF UPDATE() funtion to test for columns that have been updated.
You can also use the COLUMNS_UPDATED() function which uses a bitmap but could be a little too complicated for your needs.
March 7, 2011 at 3:01 am
UPDATE() and COLUMNS_UPDATED() will give you a column if it is mentioned in UPDATE (or insert) statement.
That is, they will give you the column even if UPDATE updates it to the same value, e.g.
UPDATE MyTable SET MyColumn = MyColumn -- UPDATE(MyColumn) will be true even though we do not change the value
That method could be very unusefull if your application's UPDATE touches all the columns.
So, you really need to compare the values in DELETED and INSERTED metatables, pairing it by primary key. They both have the same number of rows when triggered on UPDATE command. Watch out for NULL's.
Example or trigger body:
select i.MyPK,
Field1Old = nullif(d.Field1,i.Field1), Field1New = nullif(i.Field1,d.Field1), -- both populated only if there is a difference
Field2Old = nullif(d.Field2,i.Field2), Field2New = nullif(i.Field2,d.Field2),
...
Field100Old = nullif(d.Field100,i.Field100), Field1New = nullif(i.Field100,d.Field100),
INTO #diff -- creates temp table
from INSERTED i
JOIN DELETED d ON i.MyID = d.MyID -- join by primary key
-- The result
select MyPK, Field1Old, Field1New from #diff where isnull(Field1Old,Field1New) is not null
union all
select MyPK, Field2Old, Field2New from #diff where isnull(Field2Old,Field2New) is not null
...
union all
select MyPK, Field100Old, Field100New from #diff where isnull(Field100Old,Field100New) is not null
That should work faster than UNPIVOT.
March 7, 2011 at 3:05 pm
CELKO (3/6/2011)
Spend the money on a proper audit tool. It will work better, run faster and keep you out of jail. Seriously. Triggers do not record who sees the data as HIPPA and other laws require.
Not to solicit any name-dropping but do you have any leads on a proper audit tool?
What do you think of the [relatively] new Audit object in SQL Server?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply