CODEWITHSUNDEEP
performancesql-server-2012audit-trail
user3588074
user3588074

Reputation: 1

Performance impact when creating Audit trail using trigger in MS SQL Server 2012

In SQL Server 2012 database we want to create audit trail for almost all major tables on Update and Delete operations.Noramally we creating Audit Trail using trigger on each table and store it on shadow table. So there is any performance impact? if huge records updated or deleted on any table. There is anyother way to implement Audit trail?

Upvotes: 0

Views: 1222

Answers (1)

rscarter
rscarter

Reputation: 247

Typically, when I implement and audit trail for DB tables, I implement it via code, not in triggers. When implemented in code, you can provide additional context information, such as the reason the change was made, who made the change, what was the reason behind the change, etc., which is a very common business requirement. In a typical multi-layer application design, we have DAOs for each table and the business services which implement the updates are responsible for calling the separate DAOs for the core table update and the history entry insert. This approach is no good if you want a bunch of different sources directly making table updates to the DB, but it's a natural approach if you have a service-oriented architecture and your one set of services are the only way into and out of those tables.

If you implement audit trail using this approach, you of course need to make sure the audit trail record is inserted in the same transaction as the modification to the core table.

Whether this would perform better than a trigger-based approach, I couldn't say. My guess would be that if you are using bulk insert operations it may run faster, but would probably be slower in the more common scenario where you are updating/deleting one record at a time via SQL. It's another option you could explore, though.

Upvotes: 2

Related Questions