CODEWITHSUNDEEP
sql-servert-sql
suki
suki

Reputation: 63

Create audit table for a big table with a lot of columns in SQL Server

I know this question has been asked many times. My question here is I have a table which is around 8000 records but with around 25 columns. I would like to monitor any changes we make in this table. my server is only 2008.

We usually create an audit table for the specific table we monitor and record any changes into that using cursors as we usually have a lot of columns to monitor. But I don't want that this time!

Do you think instead of cursors, I can use a trigger to create a table called audit table XYZ and monitor changes in it having columns like field name, old value, new value, update_date, username?

Many thanks!

Upvotes: 0

Views: 115

Answers (1)

Dmihawk
Dmihawk

Reputation: 599

Short answer

Yes, absolutely use triggers over cursors. Cursors have a bad reputation for being misused and performing terribly, so where possible, avoid using them

Longer answer

If you have control over the application which is reading/writing to this table, consider have it build the queries for auditing instead. The thing to watch out for with an INSERT/UPDATE/DELETE trigger (which I assume is what you're going for) is that it's going to increase your write time for queries on that table, whereas writing the audit in its own query will avoid this (there is a caveat that I'll detail in the next paragraph). A consideration you also need to make is how much metadata the audit table needs to contain. For example, if your application requires users to log in, you may want to log their username to the audit table, which may not be available to a trigger. It all comes down to the purpose the audit table needs to serve for your application.

An advantage that triggers do have in this scenario is that they are bound to the same transaction as the underlying query. So if your INSERT/UPDATE/DELETE query fails and is rolled back, the audit rows which were created by the trigger will also be rolled back along with it, so you'll never end up with an audit entry for rows which never existed. If you favour writing your own audit queries over a trigger, you'll need to be careful to ensure that they are in the same transaction and get rolled back correctly in the event of an error

Upvotes: 1

Related Questions