Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

Oracle Event-Driven Processing
Oracle Tips by Burleson
 

Database triggers are, by definition, event-driven and fire when the following DML statements are executed:

  • DELETE

  • INSERT

  • UPDATE

A trigger can be further defined to fire before or after any or all of these DML statements, and can also be defined to fire at the statement level or the row level. By combining these factors, Table 7.1 can be generated to show the 12 types of database triggers.

Table 7.1 The 12 types of database triggers.

Trigger Type

Fires

Before DELETE statement level

Before each DELETE statement affects the trigger’s associated table, no matter how many rows are deleted from the table.

Before DELETE row level

For each row affected by a DELETE statement, before each row is deleted.

After DELETE row level

For each row affected by a DELETE statement, after each row is deleted.

After DELETE statement level

After each DELETE statement that affects the trigger’s associated table, no matter how many rows are deleted from the table.

Before INSERT statement level

Before each INSERT statement affects the trigger’s associated table, no matter how many rows are inserted into the table.

Before INSERT row level

For each row inserted into the table, before each row is inserted.

After INSERT row level

For each row inserted into the table, after each row is inserted.

After INSERT statement level

After each INSERT statement affects the trigger’s associated table, no matter how many rows are inserted into the table.

Before UPDATE statement level

Before each UPDATE statement affects the trigger’s associated table, no matter how many rows in the table are updated.

Before UPDATE row level

For each row updated in the table, before each row is updated.

After UPDATE row level

For each row updated in the table, after each row is updated.

After UPDATE statement level

After each UPDATE statement that affects the trigger’s associated table, no matter how many rows in the table are updated.

When PL/SQL 2.1 was implemented in Oracle 7.1, this 12-trigger limitation was removed. It’s now possible to have several triggers of the same type on a table, but Oracle cannot be forced to execute the individual triggers in any particular order. If your business rules must be enforced in a particular order, you must enforce the dependent rules using a single trigger.

While Oracle doesn’t execute triggers of the same type in the same order each time, Oracle does fire triggers of different levels in a specific order. This order is illustrated in Figure 7.1.

Figure 7.1  The execution order of database triggers.

Each row-level trigger can also have a WHEN clause defined, which is often used to replace IF-THEN processing. In Listing 7.2, the IF statement from the trigger in Listing 7.1 has been replaced with a WHEN clause.

Listing 7.2 Using a WHEN clause.

CREATE OR REPLACE
TRIGGER STUDENTS_ARIU
AFTER INSERT OR UPDATE OF overall_gpa
ON STUDENTS
FOR EACH ROW
WHEN new.overall_gpa > 3.5
BEGIN
   INSERT
   INTO   DEANS_LIST_STUDENTS
          (ssn)
   VALUES (:new.ssn);
END STUDENTS_ARIU;
/

The WHEN clause is used to specify an expression or condition that must evaluate to TRUE before the trigger will be executed.

The WHEN clause is normally used to test a column value. Any column referenced in the WHEN clause must be preceded by either the new or old keyword.

Maintainability

Like other stored PL/SQL objects, database triggers provide a single block of code that enforces a business rule when called from any other block of code. Because the business rule is enforced only in the trigger, the amount of code that has to be modified if the rule changes is drastically reduced.

Triggers provide one level of maintainability that is not provided by other stored PL/SQL objects. Because triggers are not called explicitly from code, the trigger can be redefined without affecting the functionality of code that writes to the trigger’s associated table. Because there are no calls to change when a trigger is modified, maintenance becomes even simpler.

Performance Improvement

Starting with PL/SQL version 2.3, database triggers were compiled into p-code for quicker execution. This p-code is of the same type as the p-code generated when a procedure or function is compiled and allows Oracle to directly call the executable version of the trigger.

If you’re working with an earlier version of PL/SQL, your triggers should be bare-bones calls to other stored PL/SQL objects. Complex IF-THEN logic (or other types of procedural logic) will slow the execution of your trigger because this logic must be compiled each time the trigger is executed. In this situation, it’s advisable to move trigger logic into stored procedures to improve performance.
 

             
This is an excerpt from the book "High Performance Oracle Database Automation" by Jonathan Ingram and Donald K. Burleson, Series Editor.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter