Menu
One simple method I've used in the past is basically just creating a second table whose structure mirrors the one I want to audit, and then create an update/delete trigger on the main table. Before a record is updated/deleted, the current state is saved to the audit table via the trigger.
While effective, the data in the audit table is not the most useful or simple to report off of. I'm wondering if anyone has a better method for auditing data changes?
There shouldn't be too many updates of these records, but it is highly sensitive information, so it is important to the customer that all changes are audited and easily reported on.
911
To create a new SQL Server Audit object: Expand Security and right-click Audits in SSMS. Select New Audit. You will need to create a name for the audit, and then indicate whether to store the audit data in an application security event log, event log or a file.
SQL Server Change Tracking is considered a legacy SQL Server Audit solution, that can be used to track and audit the database table DML changes by answering simple auditing questions such as, which row is changed, by providing the Primary Key of that row, and what type of change performed on that row.
DDL triggers can be used for administrative tasks such as auditing and regulating database operations. Use DDL triggers when you want to do the following: You want to prevent certain changes to your database schema. You want something to occur in the database in response to a change in your database schema.
How much writing vs. reading of this table(s) do you expect?
I've used a single audit table, with columns for Table, Column, OldValue, NewValue, User, and ChangeDateTime - generic enough to work with any other changes in the DB, and while a LOT of data got written to that table, reports on that data were sparse enough that they could be run at low-use periods of the day.
Added: If the amount of data vs. reporting is a concern, the audit table could be replicated to a read-only database server, allowing you to run reports whenever necessary without bogging down the master server from doing their work.
70
We are using two table design for this.
One table is holding data about transaction (database, table name, schema, column, application that triggered transaction, host name for login that started transaction, date, number of affected rows and couple more).
Second table is only used to store data changes so that we can undo changes if needed and report on old/new values.
Another option is to use a third party tool for this such as ApexSQL Audit or Change Data Capture feature in SQL Server.
43
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
