We are using
SQL Server 2008 for a j2ee web application. I need to maintains the audits for updates for each column with their previous value and new value on one of my table which is very heavy, around 100 columns and 10K records and increasing rapidly on daily basis. Around 200 users work with this application and updates also occur very frequently.
I know handling it via code will require very frequent connections to database.
I want to know which is a better approach for doing this. Is handling it through code will provide a better performance than using
SQL Server trigger for that.
We are using
Personally, I would use triggers for auditing.
- If they fail, then the main DML gets rolled back
- (same) The main update requires the DML to succeed
- A table will typically have several DML paths
- You may not be able to use code
- You will have direct table update that bypass code at some point
Ok, so we'll use triggers. Some notes
- Ensure they work for multiple rows: no CURSORs or loops
- Use them only for auditing: nothing else (no, say, sending emails)
- Ensure you use TRY/CATCH in the triggers: this massively improves the behaviour on rollbacks in triggers
- CASCADE foreign keys will cause child table triggers to fire: Beware nesting and recursion and don't use CASCADEs if possible
This last list also addresses sonme points in @NevilleK's answer. Triggers are one tool to use: use wisely and when appropriate and correctly.
600 updates per hour is trivial, not a problem at all for triggers (subject to cursors, loops, sending email and other stupid stuff)
There's an application that will create triggers for you - ApexSQL Audit. You can test the behavior then, but based on what you said, it's not a high transaction database, so there should be no problems. One more resource you can use to audit data changes is reading transaction logs.
I'd probably stick with triggers, for the same reasons gbn described, but if performance becomes an issue, you may want to consider using Service Broker to perform the work asynchronously (if business requirements will allow that). The typical design would be to have a trigger call a stored procedure that sends a Service Broker message, and the Service Broker queue handles the work where latency is occurring.
Here's a short (noncommercial) article I wrote that might help keep any triggers from getting out of hand: Writing Well-Behaved Triggers
You can try using the SQL Server Change Data Capture feature instead of using triggers. In Change Data Capture, the information is retrieved by periodic querying of the online transaction log. The process is asynchronous. Database performance is not affected; performance overhead is lower than with other solutions (e.g. using triggers)
SQL Server Change Data Capture requires no schema changes of the existing tables, no columns for timestamps are added to the tracked (source) tables, and no triggers are created. It captures the information and stores it in tables called change tables. For reading the change tables, Change Data Capture provides table-valued functions
Just a note about triggers regarding of performance: If you have an application that may work frequently with different database engines such, let us say an ERP that should accept any known database engines such as MySQl, MS SQl, ORACL, Inerbbase...etc triggers may become a problem in maintaining the code since triggers syntax is different for each database engine
In general, I'd recommend against using triggers for any heavy lifting - it's not a question of whether the performance is "better" or "worse" - it's that performance becomes unpredictable and issus are hard to debug.
Most performance problems follow a hockey stick curve - performance declines roughly linearly for a while until you hit a bottleneck, and then performance declines exponentially. The trick with scaling your system is to work out where your inflection point is, and what you need to do to move it further away if necessary (e.g. add more RAM, tune your queries, introduce off-line processes etc.).
In my experience, triggers are hard for developers to work with - they are effectively a side effect of the intended action (e.g. they intend to update a record in your table, and as a side effect, the trigger writes and audit entry). I've seen several cases where a change to the "normal" SQL caused the trigger to become much slower as a result of an unintended consequence of the side effect. For instance, a developer who was tuning a slow query changed an index which caused the trigger associated with that table to run MUCH more slowly.
So, I'd recommend against using triggers unless your audit requirements are literally just copying data from one table to another.