emi_sastra
asked on
Audit Trail Table
Hi All,
I want to have an audit trail, which I could track user activity on a table.
1. Write all data from a data table to audit table.
2. Write changed data from ? to audit table.
Let' s say an audit table look like :
1. Id BigInt
2. ColumnName varchar(max)
3. ColumnValue as varchar(max)
How could I do this ?
Please suggest good schema for the audit table.
Thank you.
I want to have an audit trail, which I could track user activity on a table.
1. Write all data from a data table to audit table.
2. Write changed data from ? to audit table.
Let' s say an audit table look like :
1. Id BigInt
2. ColumnName varchar(max)
3. ColumnValue as varchar(max)
How could I do this ?
Please suggest good schema for the audit table.
Thank you.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Mark,
What is activity 'N' means /
Thank you.
What is activity 'N' means /
Thank you.
'N' means new value. Wanted to differentiate between the only two states of Inserted and Deleted. Easier to match up 'pairs'.
But if your trigger is one for Insert, and another for Update and another for Deleted, then the Update trigger cn have the Delete (old)+ Insert (new) pairs.
It is entirely up to you - so long as you can easily identify the actual activity. And Update is the only activity that has both deleted and inserted.
One company I worked with insisted on 1 and 2 so when they reported, it was in sequence of delete then insert.
Does that make sense ?
But if your trigger is one for Insert, and another for Update and another for Deleted, then the Update trigger cn have the Delete (old)+ Insert (new) pairs.
It is entirely up to you - so long as you can easily identify the actual activity. And Update is the only activity that has both deleted and inserted.
One company I worked with insisted on 1 and 2 so when they reported, it was in sequence of delete then insert.
Does that make sense ?
ASKER
3 2018-02-17 05:15:32.727 51 U 1 1 1 1 1 1 2.50
4 2018-02-17 05:15:32.727 51 N 1 1 1 1 1 1 3.50
The first row is to record date time updated and also its old value ?
The second row is to record the change ?
Thank you.
4 2018-02-17 05:15:32.727 51 N 1 1 1 1 1 1 3.50
The first row is to record date time updated and also its old value ?
The second row is to record the change ?
Thank you.
ASKER
- and dont use '*'
Why ?
- P.S. I have created an all in one trigger, but you could create 3 triggers one for each of insert, update and delete.
What is the advantage and disadvantage of combine and separate it ?
Thank you.
Why ?
- P.S. I have created an all in one trigger, but you could create 3 triggers one for each of insert, update and delete.
What is the advantage and disadvantage of combine and separate it ?
Thank you.
Row ID's 3 and 4, yes, 3 is the old value (from deleted) and 4 is the new value (from inserted)
Why not '*' ? Because you have two different tables and any change to either one might break the trigger and your transactions are then compromised. For example, say I wanted to capture more information in my Audit Table, so I change the structure. Until such time I can change the trigger, it will be failing because columns to be inserted dont match columns being selected.
The advantage of having 3 triggers is being able to separately identify discrete transactions (mainly the update). Secondary benefit is not having to check for the type of activity.
Why not '*' ? Because you have two different tables and any change to either one might break the trigger and your transactions are then compromised. For example, say I wanted to capture more information in my Audit Table, so I change the structure. Until such time I can change the trigger, it will be failing because columns to be inserted dont match columns being selected.
The advantage of having 3 triggers is being able to separately identify discrete transactions (mainly the update). Secondary benefit is not having to check for the type of activity.
ASKER
'N' means new value. Wanted to differentiate between the only two states of Inserted and Deleted. Easier to match up 'pairs'.
3 2018-02-17 05:15:32.727 51 U 1 1 1 1 1 1 2.50
4 2018-02-17 05:15:32.727 51 N 1 1 1 1 1 1 3.50
Why dataId both = 1, why id 4 has dataId = 3 ?
Thank you.
3 2018-02-17 05:15:32.727 51 U 1 1 1 1 1 1 2.50
4 2018-02-17 05:15:32.727 51 N 1 1 1 1 1 1 3.50
Why dataId both = 1, why id 4 has dataId = 3 ?
Thank you.
ASKER
'N' means new value. Wanted to differentiate between the only two states of Inserted and Deleted. Easier to match up 'pairs'.
3 2018-02-17 05:15:32.727 51 U 1 1 1 1 1 1 2.50
4 2018-02-17 05:15:32.727 51 N 1 1 1 1 1 1 3.50
Why dataId both = 1, why id 4 has dataId <> 3 ?
Thank you.
3 2018-02-17 05:15:32.727 51 U 1 1 1 1 1 1 2.50
4 2018-02-17 05:15:32.727 51 N 1 1 1 1 1 1 3.50
Why dataId both = 1, why id 4 has dataId <> 3 ?
Thank you.
3 & 4 are the audit table id's - an identity column in the audit table so it auto increments every insert.
Should be a primary key.... but havent put any indexes on the example tables.
Should be a primary key.... but havent put any indexes on the example tables.
ASKER
Hi All,
Thank you very much for your help.
Thank you very much for your help.
ASKER
It is interesting.The description is huge at the link.
Would you please describe how it works ?
Thank you.