Abstract Confusions

Complexity is not a cause of confusion. It is a result of it.

Tag Archives: Trigger

Compound Trigger – A Performance Improvement for Trigger Executions

In Oracle database 11g release version 1, among the enhancements in trigger execution an important and efficient feature known as ‘Compound Trigger’ is rolled out. This feature is particularly exciting because of the performance improvements it may bring in along with it.

Auditing Bulk INSERT/UPDATE of a Table Efficiently

Assume you have a transactional table, this table is highly accessed by a front end application which does a lot of inserts and updates every minute. You also have a functional requirement of keeping track of the changes against the values inserted / updated. The traditional setup one would have for such a scenario is to write a separate trigger each for AFTER INSERT and AFTER UPDATE to fetch the values and insert them into an auditing table as well. Read more of this post

Trigger Enhancements in Oracle 11g Release 1

Prior to Oracle 11g Release 1, if you have two (or more) triggers on a table for same action – event (BEFORE, AFTER – INSERT, DELETE, UPDATE), there is no guarantee in which order there will be fired. Oracle database 11g Release 1 has introduced improvements in triggers.

On Order of Execution of Multiple Triggers

Prior to Oracle database 11g, developers can never assure you the order of execution of triggers. If you have more than one trigger set on the same triggering conditions, Oracle documentation says that, the order of execution will be random.

Oracle fires multiple triggers in an unspecified, random order, if more than one trigger of the same type exists for a given statement; that is, triggers of the same type for the same statement are not guaranteed to fire in any specific order.

This was a hindrance in many scenarios. Most of the time PL/SQL developers would do away with this in simple manner. They will write one trigger and let it to call all other similar triggering actions. Although this doesn’t look like a good practice, it was at least better than to leave it for Oracle to decide at random. Read more of this post