Manage Learn to apply best practices and optimize your operations.

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

This example demonstrates when it's best to use INSTEAD OF triggers and when to implement AFTER triggers in Microsoft SQL Server.

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

Michelle Gutzait works as a team member of the SQL Server Database Experts at, 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.

Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning