 |
|
Oracle Event-Driven
Processing
Oracle Tips by
Burleson
|
Database triggers are, by definition, event-driven and fire when
the following DML statements are executed:
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. |
