 |
|
|
Home > SQL Server trigger example: INSTEAD OF triggers vs. AFTER triggers |
|
|
|
|
|
|
SQL Server trigger example: INSTEAD OF triggers vs. AFTER triggers |
|
| 02 May 2006 | SearchSQLServer.com |
|
|
Intro | Part 1 | Part 2 | Part 3 | Part 4 | Part 5 | Part 6 | Part 7
Problem: You bought packaged software. The vendor's fee for each change is gigantic. The existing application inserts a row into a very large table, a few rows every second. The users come up with a new requirement that, upon a condition (on the new row's fields), you have to decide whether to: -
Insert the row as is.
- Not insert the row at all (30% of the time).
- Change the value of three fields before insert (30% of the time).
You decide to create a trigger, but how should you implement it?
Solution: Choose between an INSTEAD OF trigger and an AFTER trigger. If you select AFTER trigger, in 30% of the cases, you will have to roll back the insert; and for another 30% of the cases, you will have to update a row that was just inserted. This can result in having a considerable overhead for a "very large table, few rows every second."
In that case, I would choose the INSTEAD OF trigger.
Explore Microsoft SQL Server triggers
 Home Introduction
Part 1 Why use triggers in SQL Server?
Part 2 Designing and implementing 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 |
|
|
|
|
| |
