SQL Server trigger example: INSTEAD OF triggers vs. AFTER triggers

• Published: 02 May 2006

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.