 |
|
|
Home > Designing and implementing triggers in Microsoft SQL Server |
|
|
|
|
|
|
Designing and implementing triggers in Microsoft SQL Server |
|
| 02 May 2006 | SearchSQLServer.com |
|
|
Intro | Part 1 | Part 2 | Part 3 | Part 4 | Part 5 | Part 6 | Part 7
Your goal when dealing with triggers in SQL Server is the same as your goal with other database programs: Try to use as few resources as possible on the database side to achieve your objectives. Every SQL command is a resource consumer and therefore, try to group commands as much as possible if doing so helps you boost performance. -
Constraints, including check, referential integrity and cascading referential integrity, perform better than triggers. Cascading referential integrity constraints are automatic updates and deletes on dependant objects.
- INSTEAD OF triggers are "executed after the inserted and deleted tables reflecting the changes to the base table are created, but before any other actions are taken. They are executed before any constraints," according to Books Online, which means that, from a performance viewpoint, the overhead of an INSTEAD OF trigger is higher than an AFTER trigger. Try to avoid INSTEAD OF triggers except in the following two cases:
-
You need to make automatic updates to the same table after rows are inserted or after applying an update. Don't use conventional (AFTER) triggers that update the rows after you inserted and updated them. Use INSTEAD OF triggers that insert and update everything at once.
- In SQL Server rollbacks are steep. If around 50% of the time the trigger would issue a rollback, use INSTEAD OF trigger. Its overhead is less than that of an AFTER trigger that rolls back.
- You can create more than one trigger on a table (and on an action). You can also control which trigger should run first and which should run last. If you have more than two triggers on a table, you can't control the order in which the other ones fire. To optimize trigger performance, you should specify the trigger that is most likely to roll back (for whatever reason) as the first trigger to fire. This way, if the trigger does cause a rollback, less work is rolled back.
- SQL Server Books Online states, "The use of cursors in triggers is not recommended because of the potentially negative impact on performance. Use rowset-based logic rather than cursors to design a trigger that affects multiple rows."
- Avoid recursive and nested trigger types in SQL Server. The transaction length can greatly affect performance. Usually, a good application design leads you to stay away from needing those options.
- Note that triggers execute (fire) once for each command and not for each affected row, whether only one row is involved or one million rows. This fact can help us deal with a number of performance issues in SQL Server.
Explore Microsoft SQL Server triggers
 Home Introduction
Part 1 Why use triggers in SQL Server?
Part 2 Design and implement triggers in SQL Server
Part 3 Example: INSTEAD OF triggers vs. AFTER triggers
Part 4 Example: One trigger vs. two for the same logic
Part 5 Example: Nested triggers in SQL Server
Part 6 Example: Heavy insertion process from OLTP and triggers
Part 7 Example: Cursors in SQL Server triggers
| ABOUT THE AUTHOR: |
|
Michelle Gutzait works as a team member of the SQL Server Database Experts at www.pythian.com, a worldwide company providing Remote Administration services for databases, operating systems, SANs and networks. Gutzait has been involved in IT for 20 years as a developer, business analyst and database consultant. For the past 10 years, she has worked exclusively with SQL Server. Her skills include SQL infrastructure and database design, performance tuning, security, high availability, disaster recovery, very large databases, replication, T-SQL coding, DTS/SSIS packages, administrative and infrastructure tools development, reporting services and more. |
|
|
|
|
|
| TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of . |
|
| |
All Rights Reserved, , TechTarget |
|
|
|
|
| |
