Example: INSTEAD OF triggers versus AFTER triggers

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.

How to optimize database triggers

 Home: Introduction
 Part 1: Why use triggers?
 Part 2: How to design and implement triggers
 Part 3: Example: INSTEAD OF triggers versus AFTER triggers
 Part 4: Example: One trigger versus two for the same logic
 Part 5: Example: Nested triggers
 Part 6: Example: Heavy insertion process from OLTP and triggers
 Part 7: Example: Cursors in triggers

ABOUT THE AUTHOR:

Michelle Gutzait Michelle Gutzait works as a senior databases consultant for ITERGY International Inc., an IT consulting firm specializing in the design, implementation, security and support of Microsoft products in the enterprise. 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 database design, performance tuning, security, high availability, disaster recovery, very large databases, replication, T-SQL coding, DTS packages, and administrative and infrastructure tools development, reporting services and more. Copyright 2006 TechTarget

More from SearchSQLServer.com