Last post Jun 10, 2011 02:26 AM by Zephyr684
Jun 07, 2011 08:24 AM|Zephyr684|LINK
I know this question has been asked a lot of times but may be i have not understood the proposed solutions or my requirement is a bit different. We are using Asp.net with Sql server 2008 and Linq to Sql as our DAL. As part of the project we need to maintian
the aduit in the db and the below is the summary of key requirements,
1. We need to keep an audit trail of alomost all the tables in our DB which has around 370 tables.
2. Along with the inforamtion in the table we want to keep track of who and when added/modified/deleted records.
3. The 'who' here is the Asp.net application users and not the db user. We are using form authentication with custom username and password.
We have considered a few options like Sql CDC and Sql server audit but to no avail as none of these satisfy our requirement fully.
1. Sql CDC:While using Sql CDC we added a column for 'Who' to each table to store the id of the user who is adding/deleteing/updating the information. All seemed to work well until we were caught up with the delete operation. When creating an audit record
for a row being deleted the newly created delete record entry in the CDC table had the same user id who modified/addded the record last time around.
2. Sql Audit: As for Sql audit the same question about which asp.net user add/modified/deleted arises and also i guess with will not help in maintaining the change data records.
If someone can help me out with how exactly to go about auditing in web applications and mainting the inforamtion about which application user triggered the change it will be really helpful.
Jun 10, 2011 02:26 AM|Zephyr684|LINK
After looking around a bit and discussing it internally we found a probable solution. In this approach we went ahead with Sql CDC as it definetly solved our problem while inserting and updating records after adding an additonal column for ModifiedBy. As
for delete we still take help from the CDC but with some extra added wiring. We added an Extra table say 'TblDeleteTracker' with columns namely TableName,PrimaryKeyValue, UserID for name of the table for which delete took place, value of the primary key for
the row being deleted and the userid for user who deleted the row. Also we added an 'After Insert trigger' on each CDC table.
Now before calling the actual delete action we make an entry in the TblChangeTracker about the table who's row is being deleted ,the primary key value of the row being deleted and the id of the user initaiting the delete action. After the delete action takes
place some time in future the CDC will run and insert an entry in the corresponding cdc table. It is at this point the 'After insert' trigger for that table fires and does the below,
1. Get the userID from the ChangeTracker table based on the Priamry key value from the 'Inserted' record and the current table's name.
2. Update the newly inserted row in the current CDC table only if the operation was of type delete.
3. Delete the record from the ChangeTracker table.
Hope this helps.