SQL Triggers - Design for Performance
SQL Triggers are a powerful tool, but be careful not to overuse them.I have just finished investigating a performance problem for a client, and one of the contributing factors was inefficient use of triggers.
A trigger is useful in the following situations:
- Auditing Data Modification.
- Maintaining Denormalized Columns.
- Instead of referential integrity. Always use declarative referential integrity wherever possible.
- Preventing data modification by rolling back the transaction. This is best handled within the code performing the modification, thus preventing the trigger from being called.
- Processing that is complicated or requires accessing several tables or reading large amounts of data.
Some ways of speeding up a trigger are:
- Create indexes to support your queries.
- Use the UPDATE() function and exit the trigger immediately if the columns you are interested in have not been changed.
- Avoid nested triggers. They contribute further to poor performance and can make the code very difficult to debug. The same applies to recursive triggers.
- Avoid using cursors. Actually this advice applies generally; not just to triggers.
- Remember that a trigger fires once for an entire update, insert or delete, not once per row. This allows you to code it more efficiently for cases where multiple rows are affected.
Well personally I have never seen a situation where triggers are the only option. I avoid them at all costs, but if you really do have no alternative you should design them with performance in mind. A few badly thought out triggers can turn an otherwise well designed system into something the users never stop complaining about.
Not found what you're looking for?
Use this search box. It is tuned for SQL Server searches. Try it and see!Do you have a question about SQL Server? Would you like to answer a question?
Go to the SQL FAQ to get started.
