Triggers

This chapter discusses triggers, which are procedures written in PL/SQL, Java, or C that execute ("fire") implicitly whenever a table or view is modified, or when some user actions or database system actions occur. You
can write triggers that fire whenever one of the following operations occurs:
DML statements on a particular schema object, DDL statements issued within a schema or database, user logon or logoff events, server errors, database startup, or instance shutdown.

This chapter includes:

•   An Introduction to Triggers
•   Parts of a Trigger
•   Types of Triggers
•   Trigger Execution

An Introduction to Triggers

Oracle allows you to define procedures called triggers that execute implicitly when an INSERT, UPDATE, or DELETE statement is issued against the associated table (or, in some cases, against a view) or when database system actions occur. These procedures can be written in PL/SQL or Java and stored in the database, or they can be written as C callouts. 

Triggers are similar to stored procedures. A trigger stored in the database can include SQL and PL/SQL or Java statements to execute as a unit and can invoke stored procedures. However, procedures and triggers differ in the
way that they are invoked. A procedure is explicitly executed by a user, application, or trigger. Triggers (one or more) are implicitly fired (executed) by Oracle
when a triggering event occurs, no matter which user is connected or which application is being used.

A trigger can also call out to a C procedure, which is useful for computationally intensive operations.

The events which fire a trigger can be DML statements that modify data in a table (INSERT, UPDATE, or DELETE), DDL statements, system events such as startup, shutdown, and error messages, or user events such as logon and logoff.

Note:

Oracle Forms can define, store, and execute triggers of a different sort. However, do not confuse Oracle Forms triggers with the triggers discussed in this chapter.

How Triggers Are Used  

Triggers can supplement the standard capabilities of Oracle to provide a highly customized database management system. For example, a trigger can restrict DML operations against a table to those issued during regular business
hours. A trigger could also restrict DML operations to occur only at certain times during weekdays. Other uses for triggers are to 

• automatically generate derived column values
• prevent invalid transactions
• enforce complex security authorizations
• enforce referential integrity across nodes in
       a distributed database
• enforce complex business rules
• provide transparent event logging
• provide sophisticated auditing
• maintain synchronous table replicates
• gather statistics on table access
• modify table data when DML statements are
       issued against views
• publish information about database events,
       user events, and SQL statements to subscribing applications 
  
   

Some Cautionary Notes about Triggers

Although triggers are useful for customizing a database, you should use
them only when necessary. Excessive use of triggers can result in complex
interdependencies, which may be difficult to maintain in a large application.
For example, when a trigger fires, a SQL statement within its trigger action
potentially can fire other triggers, resulting in cascading triggers.

You can use both triggers and integrity constraints to define and
enforce any type of integrity rule. However, Oracle Corporation strongly
recommends that you use triggers to constrain data input only in the following
situations:

• to enforce referential integrity when child and
       parent tables are on different nodes of a distributed database
• to enforce complex business rules not
       definable using integrity constraints
• when a required referential integrity rule
       cannot be enforced using the following integrity constraints:
 
• NOT
        NULL, UNIQUE key
 
• PRIMARY
        KEY
 
• FOREIGN
        KEY
 
• CHECK
       
 
• DELETE
        CASCADE
 
• DELETE
        SET NULL

Parts of a Trigger

A trigger has three basic parts:

• a triggering event or statement
• a trigger restriction
• a trigger action

The sections that follow explain each part of a trigger in greater
detail.

Triggering
Event or Statement

A triggering event or statement is the SQL statement, database event, or
user event that causes a trigger to be fired. A triggering event can be one or
more of the following:

• an INSERT, UPDATE, or DELETE statement on a
       specific table (or view, in some cases)
• a CREATE, ALTER, or DROP statement on any
       schema object
• a database startup or instance shutdown
• a specific error message or any error message
• a user logon or logoff

. . . UPDATE OF parts_on_hand ON inventory . . .

which means: when the PARTS_ON_HAND column of a row in the INVENTORY
table is updated, fire the trigger. Note that when the triggering event is an
UPDATE statement, you can include a column list to identify which columns must be
updated to fire the trigger. You cannot specify a column list for INSERT and
DELETE statements, because they affect entire rows of information.

A triggering event can specify multiple SQL statements, as in

. . . INSERT OR UPDATE OR DELETE OF inventory . . .

which means: when an INSERT, UPDATE, or DELETE statement is issued
against the INVENTORY table, fire the trigger. When multiple types of SQL
statements can fire a trigger, you can use conditional predicates to detect the
type of triggering statement. In this way, you can create a single trigger that
executes different code based on the type of statement that fires the trigger.

Trigger Restriction

A trigger restriction specifies a Boolean (logical) expression that must
be TRUE for the trigger to fire. The trigger action is not executed if the
trigger restriction evaluates to FALSE or UNKNOWN. In the example, the trigger
restriction is

new.parts_on_hand < new.reorder_point

Trigger Action

A trigger action is the procedure (PL/SQL block, Java program, or C
callout) that contains the SQL statements and code to be executed when a
triggering statement is issued and the trigger restriction evaluates to TRUE.

Like stored procedures, a trigger action can contain SQL and PL/SQL or
Java statements, define PL/SQL language constructs (variables, constants,
cursors, exceptions, and so on) or Java language constructs, and call stored
procedures. Additionally, for row triggers (described in the next section), the
statements in a trigger action have access to column values (new and old) of
the current row being processed by the trigger. Correlation names provide
access to the old and new values for each column.

Types of Triggers

This section describes the different types of triggers:

•   Row Triggers and Statement Triggers

•   BEFORE and AFTER Triggers

•   INSTEAD-OF Triggers

•   Triggers on System Events and User Events

Row Triggers and Statement Triggers

When you define a trigger, you can specify the number of times the
trigger action is to be executed: once for every row affected by the triggering
statement (such as might be fired by an UPDATE statement that updates many
rows), or once for the triggering statement, no matter how many rows it
affects.

Row Triggers

A row trigger is fired each time the table is affected by the
triggering statement. For example, if an UPDATE statement updates multiple rows
of a table, a row trigger is fired once for each row affected by the UPDATE
statement. If a triggering statement affects no rows, a row trigger is not
executed at all.

Row triggers are useful if the code in the trigger action depends on
data provided by the triggering statement or rows that are affected.

Statement Triggers

A statement trigger is fired once on behalf of the triggering
statement, regardless of the number of rows in the table that the triggering
statement affects (even if no rows are affected). For example, if a DELETE
statement deletes several rows from a table, a statement-level DELETE trigger
is fired only once, regardless of how many rows are deleted from the table.

Statement triggers are useful if the code in the trigger action does not
depend on the data provided by the triggering statement or the rows affected.
For example, if a trigger makes a complex security check on the current time or
user, or if a trigger generates a single audit record based on the type of triggering statement, a statement trigger is used.

BEFORE and AFTER Triggers

When defining a trigger, you can specify the trigger timing--whether
the trigger action is to be executed before or after the triggering statement.
BEFORE and AFTER apply to both statement and row triggers.

BEFORE and AFTER triggers fired by DML statements can be defined only on
tables, not on views. However, triggers on the base table(s) of a view are
fired if an INSERT, UPDATE, or DELETE statement is issued against the view.
BEFORE and AFTER triggers fired by DDL statements can be defined only on the
database or a schema, not on particular tables.

BEFORE Triggers

BEFORE triggers execute the trigger action before the triggering
statement is executed. This type of trigger is commonly used in the following
situations:

• When the trigger action should determine
       whether the triggering statement should be allowed to complete. Using a
       BEFORE trigger for this purpose, you can eliminate unnecessary processing
       of the triggering statement and its eventual rollback in cases where an
       exception is raised in the trigger action.
• To derive specific column values before
       completing a triggering INSERT or UPDATE statement.

AFTER Triggers

AFTER triggers execute the trigger action after the triggering statement
is executed. AFTER triggers are used when you want the triggering statement to
complete before executing the trigger action.

Trigger Type Combinations

Using the options listed above, you can create four types of row and
statement triggers:

• BEFORE
       statement trigger

Before executing the triggering statement, the
trigger action is executed.

• BEFORE
       row trigger

Before modifying each row affected by the
triggering statement and before checking appropriate integrity constraints, the
trigger action is executed provided that the trigger restriction was not
violated.

• AFTER
       row trigger

After modifying each row affected by the triggering
statement and possibly applying appropriate integrity constraints, the trigger
action is executed for the current row provided the trigger restriction was not
violated. Unlike BEFORE row triggers, AFTER row triggers lock rows.

• AFTER
       statement trigger

After executing the triggering statement and
applying any deferred integrity constraints, the trigger action is executed.

You can have multiple triggers of the same type for the same statement
for any given table. For example you may have two BEFORE statement triggers for
UPDATE statements on the EMP table. Multiple triggers of the same type permit
modular installation of applications that have triggers on the same tables.
Also, Oracle snapshot logs use AFTER row triggers, so you can design your own
AFTER row trigger in addition to the Oracle-defined AFTER row trigger.

You can create as many triggers of the preceding different types as you
need for each type of DML statement (INSERT, UPDATE, or DELETE).

For example, suppose you have a table, SAL, and you want to know when
the table is being accessed and the types of queries being issued. The example
below contains a sample package and trigger that tracks this information by
hour and type of action (for example, UPDATE, DELETE, or INSERT) on table SAL.
A global session variable, STAT.ROWCNT, is initialized to zero by a BEFORE
statement trigger. Then it is increased each time the row trigger is executed.
Finally the statistical information is saved in the table STAT_TAB by the AFTER
statement trigger.

Sample Package and Trigger for SAL Table

DROP TABLE stat_tab;

CREATE TABLE stat_tab(utype CHAR(8),

      rowcnt INTEGER, uhour
INTEGER);

CREATE OR REPLACE PACKAGE stat IS

 rowcnt INTEGER;

END;

/

CREATE TRIGGER bt BEFORE UPDATE OR DELETE OR INSERT ON sal

BEGIN

 stat.rowcnt := 0;

END;

/

CREATE TRIGGER rt BEFORE UPDATE OR DELETE OR INSERT ON sal

FOR EACH ROW BEGIN

 stat.rowcnt := stat.rowcnt + 1;

END;

/

CREATE TRIGGER at AFTER UPDATE OR DELETE OR INSERT ON sal

DECLARE

 typ  CHAR(8);

 hour NUMBER;

BEGIN

 IF updating

 THEN typ := 'update'; END IF;

 IF deleting  THEN typ := 'delete'; END IF;

 IF inserting THEN typ :=
'insert'; END IF;

 hour := TRUNC((SYSDATE -
TRUNC(SYSDATE)) * 24);

 UPDATE stat_tab

    SET rowcnt = rowcnt +
stat.rowcnt

   WHERE utype = typ

     AND uhour = hour;

 IF SQL%ROWCOUNT = 0 THEN

   INSERT INTO stat_tab VALUES
(typ, stat.rowcnt, hour);

 END IF;

EXCEPTION

 WHEN dup_val_on_index THEN

   UPDATE stat_tab

      SET rowcnt = rowcnt +
stat.rowcnt

     WHERE utype = typ

       AND uhour = hour;

END;

/

INSTEAD-OF Triggers

Attention:

INSTEAD-OF triggers are available only if you have purchased the Oracle8i
Enterprise Edition. They can be used with relational views and object views.

INSTEAD-OF triggers provide a transparent way of modifying views that
cannot be modified directly through SQL DML statements (INSERT, UPDATE, and
DELETE). These triggers are called INSTEAD-OF triggers because, unlike other
types of triggers, Oracle fires the trigger instead of executing the triggering
statement.

You can write normal INSERT, UPDATE, and DELETE statements against the
view and the INSTEAD-OF trigger is fired to update the underlying tables
appropriately. INSTEAD-OF triggers are activated for each row of the view that
gets modified.

Modifying Views

Modifying views has inherent problems of ambiguity.

• Deleting a row in a view could either mean
       deleting it from the base table or updating some column values so that it
       will no longer be selected by the view.
• Inserting a row in a view could either mean
       inserting a new row into the base table or updating an existing row so
       that it will be projected by the view.
• Updating a column in a view that involves
       joins might change the semantics of other columns that are not projected
       by the view.

For example, a key use of object views is to represent master/detail
relationships. This inevitably involves joins, but modifying joins is
inherently ambiguous.

As a result of these ambiguities, there are many restrictions on which
views are modifiable (see the next section). An INSTEAD-OF trigger can be used
on object views as well as relational views that are not otherwise modifiable.

Even if the view is inherently modifiable, you might want to perform
validations on the values being inserted, updated or deleted. INSTEAD-OF
triggers can also be used in this case. Here the trigger code would perform the
validation on the rows being modified and if valid, propagate the changes to
the underlying tables.

INSTEAD-OF triggers also enable you to modify object view instances on
the client-side through OCI. To modify an object materialized by an object view
in the client-side object cache and flush it back to the persistent store, you
must specify INSTEAD-OF triggers, unless the object view is inherently
modifiable. However, it is not necessary to define these triggers for just
pinning and reading the view object in the object cache.

<> <>   <> <>

<> <>   <> <>

Views That Are Not Modifiable

A view is inherently modifiable if it can be inserted, updated,
or deleted without using INSTEAD-OF triggers and if it conforms to the
restrictions listed below. If the view query contains any of the following
constructs, the view is not inherently modifiable and you therefore cannot
perform inserts, updates, or deletes on the view:

• set operators
• aggregate functions
• GROUP BY, CONNECT BY, or START WITH clauses
• the DISTINCT operator
• joins (however, a subset of join views are
       updatable)

If a view contains pseudocolumns or expressions, you can only update the
view with an UPDATE statement that does not refer to any of the pseudocolumns
or expressions.

Example of an INSTEAD OF Trigger

The following example shows an INSTEAD OF trigger for updating rows in
the manager_info view, which lists all the departments and their managers.

Let dept be a relational table containing a list of departments,

CREATE TABLE dept (

   deptno NUMBER PRIMARY KEY,

   deptname VARCHAR2(20),

   manager_num NUMBER

);

Let emp be a relational table containing the list of employees and the
departments in which they work.

CREATE TABLE emp (

   empno NUMBER PRIMARY KEY,

   empname VARCHAR2(20),

   deptno NUMBER REFERENCES
dept(deptno),

   startdate DATE

);

ALTER TABLE dept ADD (FOREIGN KEY(manager_num) REFERENCES emp(empno));

Create the manager_info view that lists all the managers for each
department:

CREATE VIEW manager_info AS

  SELECT d.deptno, d.deptname,
e.empno, e.empname

  FROM   emp e, dept d

  WHERE  e.empno = d.manager_num;

Now, define an INSTEAD-OF trigger to handle the inserts on the view. An
insert into the manager_info view can be translated into an update to the
manager_num column of the dept table.

In the trigger, you can also enforce the constraint that there must be
at least one employee working in the department for a person to be a manager of
that department.

CREATE TRIGGER manager_info_insert

  INSTEAD OF INSERT ON
manager_info

    REFERENCING NEW AS n              -- new manager information

  FOR EACH ROW

  DECLARE

    empCount NUMBER;

  BEGIN

   

     /* First check to make sure
that the number of employees

      * in the department is
greater than one */

     SELECT COUNT(*) INTO empCount

      FROM emp e

      WHERE e.deptno = :n.deptno;

 

     /* If there are enough
employees then make him or her the manager */

     IF empCount >= 1 THEN

        UPDATE dept d 

         SET manager_num =
:n.empno

         WHERE d.deptno =
:n.deptno;

     END IF;

  END;

  /

Any inserts to the manager_info view, such as:

INSERT INTO manager_info VALUES (200,'Sports',1002,'Jack');

will fire the manager_info_insert trigger and update the underlying
tables. Similar triggers can specify appropriate actions for INSERT and DELETE
on the view.

Usage Notes

The INSTEAD OF option to the CREATE TRIGGER statement can only be
used for triggers created over views. The BEFORE and AFTER options cannot
be used for triggers created over views.

The CHECK option for views is not enforced when inserts or updates to
the view are done using INSTEAD OF triggers. The INSTEAD OF trigger body must
enforce the check.

<> <>   <> <>

<>    <>     <>    <>       INSTEAD-OF Triggers   on Nested Tables

You cannot modify the elements of a nested table column in a view
directly with the TABLE clause. However, you can do so by defining an INSTEAD
OF trigger on the nested table column of the view. The triggers on the nested
tables fire if a nested table element is updated, inserted, or deleted and
handle the actual modifications to the underlying tables.

Consider the department-employee example again. Let the department view
contain the list of departments and the set of employees in each department.
The following example shows how to modify the elements of the nested table of
employee objects in the department view using INSTEAD-OF triggers.

/* Create an employee type */

CREATE TYPE emp_t AS OBJECT

(

  empno NUMBER,

  empname VARCHAR2(20),

  days_worked NUMBER

);

/

/* Create a nested table type of employees */

CREATE TYPE emplist_t AS TABLE OF emp_t;

/

/* Now, create the department type */

CREATE TYPE dept_t AS OBJECT

(

  deptno NUMBER,

  deptname VARCHAR2(20),

  emplist  emplist_t

);

/

/* The dept_view can now be created based on the dept (department) and
emp

 * (employee) tables. */

CREATE VIEW dept_view OF dept_t WITH OBJECT OID(deptno)

  AS SELECT d.deptno, d.deptname,
-- department number and name

       CAST (MULTISET (

                SELECT e.empno, e.empname, (SYSDATE
- e.startdate)

                FROM   emp e

                WHERE  e.deptno = d.deptno)

            AS emplist_t)       -- emplist - nested table of employees

     FROM dept d;

To be able to insert into the nested table emplist in the view
using the TABLE syntax:

INSERT INTO TABLE

   (SELECT d.emplist FROM
dept_view d WHERE d.deptno = 10)

   VALUES (10,'Harry Mart',334);

you can define an INSTEAD-OF trigger on the nested table emplist to
handle the insert, such as:

CREATE TRIGGER dept_empinstr INSTEAD OF INSERT ON

   NESTED TABLE emplist OF
dept_view FOR EACH ROW

BEGIN

    INSERT INTO emp
VALUES(:NEW.empno, :NEW.empname,

                           :PARENT.deptno, SYSDATE - :NEW.days_worked);

END;

/

Similarly you can define triggers to handle updates and deletes on the
nested table elements.

Accessing the Attributes of the Parent Row Inside a Nested Table Trigger

In regular triggers, the current row's values can be accessed using the
NEW and OLD qualifiers. For triggers on nested table columns of views, these
qualifiers refer to the attributes of the nested table element being modified.
In order to access the values of the parent row containing this nested table
column, you can use the PARENT qualifier.

This qualifier can be used only inside these nested table triggers. The
parent row's values obtained using this PARENT qualifier cannot be modified
(that is, they are read-only).

Consider the dept_empinstr trigger example shown above. The NEW
qualifier refers to the row of the nested table being inserted (that is, it
contains empno, empname and days_worked) and does not
include the department number (deptno) where the employee works.
But you need to insert the department number into the employee table inside the
trigger. This deptno value can be obtained from the parent row that
contains the list of employees, using the PARENT qualifier.

Firing of Nested Table and View
Triggers

As explained before, if a nested table column in a view has an INSTEAD-OF
trigger defined over it, then when an element of that nested table is inserted,
updated, or deleted, the trigger is fired to do the actual modification.

The view containing the nested table column need not have any INSTEAD-OF
triggers defined over it for this to work. Any triggers defined on the view
will not fire for any modifications to the nested table elements.

Conversely, a statement that modifies a row in the view would only fire
the triggers defined on the view and not those on the nested table columns of
that view. For instance if the emplist nested table column is updated through
the dept_view as in:

UPDATE dept_view SET emplist = emplist_t(emp_t(1001,'John',234));

it will fire the INSTEAD-OF update triggers defined over the dept_view,
if any, but not the dept_empinstr nested table trigger.

Triggers on System Events and User Events

You can use triggers to publish information about database events to
subscribers. Applications can subscribe to database events just as they
subscribe to messages from other applications. These database events can
include:

• system events
 
• database
        startup and shutdown
 
• server
        error message events
• user events
 
• user
        logon and logoff
 
• DDL
        statements (CREATE, ALTER, and DROP)
 
• DML statements (INSERT, DELETE, and UPDATE)

Triggers on system events can be defined at the database level or schema
level. For example, a database shutdown trigger is defined at the database
level:

CREATE TRIGGER register_shutdown

  ON DATABASE

  SHUTDOWN

    BEGIN

    ...

    DBMS_AQ.ENQUEUE(...);

    ...

    END;

   

Triggers on DDL statements or logon/logoff events can also be defined at
the database level or schema level. Triggers on DML statements can be defined
on a table or view. A trigger defined at the database level fires for all
users, and a trigger defined at the schema or table level fires only when the
triggering event involves that schema or table.

Event Publication

A queue serves as a message repository for subjects of interest
to various subscribers. Triggers use the DBMS_AQ package to enqueue a message
when specific system or user events occur.

Event Attributes

Each event allows the use of attributes within the trigger text. For
example, the database startup and shutdown triggers have attributes for the
instance number and the database name, and the logon and logoff triggers have
attributes for the username. You can specify a function with the same name as
an attribute when you create a trigger if you want to publish that attribute
when the event occurs. The attribute's value is then passed to the function or
payload when the trigger fires. For triggers on DML statements, this is done
with the :NEW and :OLD column values.

System Events

System events that can fire triggers are related to instance startup and
shutdown and error messages. Triggers created on startup and shutdown events
have to be associated with the database; triggers created on error events can
be associated with the database or with a schema.

• STARTUP triggers fire when the database is
       opened by an instance. Their attributes include the system event, instance
       number, and database name.
• SHUTDOWN triggers fire just before the server
       starts shutting down an instance. You can use these triggers to make
       subscribing applications shut down completely when the database shuts
       down. (For abnormal instance shutdown these triggers may not be fired.)
       The attributes of SHUTDOWN triggers include the system event, instance
       number, and database name.
• SERVERERROR triggers fire when a specified
       error occurs, or when any error occurs if no error number is specified.
       Their attributes include the system event and error number.

User Events

User events that can fire triggers are related to user logon and logoff,
DDL statements, and DML statements.

Triggers on LOGON and LOGOFF Events

LOGON and LOGOFF triggers can be associated with the database or with a
schema. Their attributes include the system event and username, and they can
specify simple conditions on USERID and USERNAME.

• LOGON triggers fire after a successful logon
       of a user.
• LOGOFF triggers fire at the start of a user
       logoff.

Triggers on DDL Statements

DDL triggers can be associated with the database or with a schema. Their
attributes include the system event, the type of schema object, and its name.
They can specify simple conditions on the type and name of the schema object,
as well as functions like USERID and USERNAME.

• BEFORE CREATE and AFTER CREATE triggers fire
       when a schema object is created in the database or schema.
• BEFORE ALTER and AFTER ALTER triggers fire
       when a schema object is altered in the database or schema.
• BEFORE DROP and AFTER DROP triggers fire when
       a schema object is dropped from the database or schema.

Triggers on DML Statements

DML triggers for event publication are associated with a table. They can
be either BEFORE or AFTER triggers that fire for each row on which the
specified DML operation occurs. You cannot use INSTEAD OF triggers on views to
publish events related to DML statements--instead, you can publish events using
BEFORE or AFTER triggers for the DML operations on a view's underlying tables
that are caused by INSTEAD OF triggers.

The attributes of DML triggers for event publication include the system
event and the columns defined by the user in the SELECT list. They can specify
simple conditions on the type and name of the schema object, as well as
functions (such as UID, USER, USERENV, and SYSDATE), pseudocolumns, and
columns. The columns may be prefixed by :OLD and :NEW for old and new values.

• BEFORE INSERT and AFTER INSERT triggers fire
       for each row inserted into the table.
• BEFORE UPDATE and AFTER UPDATE triggers fire
       for each row updated in the table.
• BEFORE DELETE and AFTER DELETE triggers fire
       for each row deleted from the table.

<> <>   <> <>

•  

<> <>   <> <>

Trigger Execution

A trigger can be in either of two distinct modes:

<> <>   <>   <> <> <>   <>   <> <>

enabled	An enabled trigger executes its trigger action if   a triggering statement is issued and the trigger restriction (if any)   evaluates to TRUE.
disabled	A disabled trigger does not execute its trigger   action, even if a triggering statement is issued and the trigger restriction   (if any) would evaluate to TRUE.

<> <>   <> <>

For enabled triggers, Oracle automatically

• executes triggers of each type in a planned
       firing sequence when more than one trigger is fired by a single SQL
       statement
• performs integrity constraint checking at a
       set point in time with respect to the different types of triggers and
       guarantees that triggers cannot compromise integrity constraints
• provides read-consistent views for queries and
       constraints
• manages the dependencies among triggers and
       schema objects referenced in the code of the trigger action
• uses two-phase commit if a trigger updates
       remote tables in a distributed database
• fires multiple triggers in an unspecified
       order, if more than one trigger of the same type exists for a given
       statement

The Execution Model for Triggers and
Integrity Constraint Checking

A single SQL statement can potentially fire up to four types of
triggers: BEFORE row triggers, BEFORE statement triggers, AFTER row triggers,
and AFTER statement triggers. A triggering statement or a statement within a
trigger can cause one or more integrity constraints to be checked. Also,
triggers can contain statements that cause other triggers to fire (cascading
triggers).

Oracle uses the following execution model to maintain the proper firing
sequence of multiple triggers and constraint checking:

1. Execute
all BEFORE statement triggers that apply to the statement.

2. Loop for
each row affected by the SQL statement.

a. Execute
all BEFORE row triggers that apply to the statement.

b. Lock and
change row, and perform integrity constraint checking. (The lock is not
released until the transaction is committed.)

c. Execute
all AFTER row triggers that apply to the statement.

3. Complete
deferred integrity constraint checking.

4. Execute
all AFTER statement triggers that apply to the statement.

The definition of the execution model is recursive. For example, a given
SQL statement can cause a BEFORE row trigger to be fired and an integrity
constraint to be checked. That BEFORE row trigger, in turn, might perform an
update that causes an integrity constraint to be checked and an AFTER statement
trigger to be fired. The AFTER statement trigger causes an integrity constraint
to be checked. In this case, the execution model executes the steps
recursively, as follows:

1. Original SQL statement issued.

2. BEFORE row triggers fired.

3. AFTER statement triggers fired by UPDATE in
BEFORE row trigger.

4. Statements of AFTER statement triggers executed.

5. Integrity constraint checked on tables changed
by AFTER statement triggers.

6. Statements of BEFORE row triggers executed.

7. Integrity constraint checked on tables changed
by BEFORE row triggers.

8. SQL statement executed.

9. Integrity constraint from SQL statement checked.

There are two exceptions to this recursion:

• When a triggering statement modifies one table
       in a referential constraint (either the primary key or foreign key table),
       and a triggered statement modifies the other, only the triggering
       statement will check the integrity constraint. This allows row triggers to
       enhance referential integrity.
• Statement triggers fired due to DELETE CASCADE
       and DELETE SET NULL are fired before and after the user DELETE statement,
       not before and after the individual enforcement statements. This prevents
       those statement triggers from encountering mutating errors.

An important property of the execution model is that all actions and
checks done as a result of a SQL statement must succeed. If an exception is
raised within a trigger, and the exception is not explicitly handled, all
actions performed as a result of the original SQL statement, including the
actions performed by fired triggers, are rolled back. Thus, integrity
constraints cannot be compromised by triggers. The execution model takes into
account integrity constraints and disallows triggers that violate declarative
integrity constraints.

For example, in the previously outlined scenario, suppose that Steps 1
through 8 succeed; however, in Step 9 the integrity constraint is violated. As
a result of this violation, all changes made by the SQL statement (in Step 8),
the fired BEFORE row trigger (in Step 6), and the fired AFTER statement trigger
(in Step 4) are rolled back.

Note:

Although triggers of different types are fired in a specific order,
triggers of the same type for the same statement are not guaranteed to fire in
any specific order. For example, all BEFORE row triggers for a single UPDATE
statement may not always fire in the same order. Design your applications so
they do not rely on the firing order of multiple triggers of the same type.

Data Access for Triggers

When a trigger is fired, the tables referenced in the trigger action
might be currently undergoing changes by SQL statements in other users'
transactions. In all cases, the SQL statements executed within triggers follow
the common rules used for standalone SQL statements. In particular, if an
uncommitted transaction has modified values that a trigger being fired either
needs to read (query) or write (update), the SQL statements in the body of the
trigger being fired use the following guidelines:

• Queries see the current read-consistent
       snapshot of referenced tables and any data changed within the same
       transaction.
• Updates wait for existing data locks to be
       released before proceeding.

The following examples illustrate these points.

Example:

Assume that the SALARY_CHECK trigger (body) includes the following
SELECT statement:

SELECT minsal, maxsal INTO minsal, maxsal

  FROM salgrade

  WHERE job_classification =
:new.job_classification;

For this example, assume that transaction T1 includes an update to the
MAXSAL column of the SALGRADE table. At this point, the SALARY_CHECK trigger is
fired by a statement in transaction T2. The SELECT statement within the fired
trigger (originating from T2) does not see the update by the uncommitted
transaction T1, and the query in the trigger returns the old MAXSAL value as of
the read-consistent point for transaction T2.

Example:

Assume that the TOTAL_SALARY trigger maintains a derived column that
stores the total salary of all members in a department:

CREATE TRIGGER total_salary

AFTER DELETE OR INSERT OR UPDATE OF deptno, sal ON emp

 FOR EACH ROW BEGIN

  /* assume that DEPTNO and SAL
are non-null fields */

   IF DELETING OR (UPDATING AND
:old.deptno != :new.deptno)

   THEN UPDATE dept

  SET total_sal = total_sal -
:old.sal

  WHERE deptno = :old.deptno;

   END IF;

   IF INSERTING OR (UPDATING AND
:old.deptno != :new.deptno)

   THEN UPDATE dept

    SET total_sal = total_sal +
:new.sal

    WHERE deptno = :new.deptno;

   END IF;

   IF (UPDATING AND :old.deptno =
:new.deptno AND

    :old.sal != :new.sal )

   THEN UPDATE dept

    SET total_sal = total_sal -
:old.sal + :new.sal

   WHERE deptno = :new.deptno;

  END IF;

 END;

For this example, suppose that one user's uncommitted transaction
includes an update to the TOTAL_SAL column of a row in the DEPT table. At this
point, the TOTAL_SALARY trigger is fired by a second user's SQL statement.
Because the uncommitted
transaction of the first user contains an update to a pertinent value in the
TOTAL_SAL column (in other words, a row lock is being held), the updates
performed by the TOTAL_SALARY trigger are not executed until the transaction
holding the row lock is committed or rolled back. Therefore, the second user
waits until the commit or rollback point of the first user's transaction.

Storage of PL/SQL Triggers

Oracle stores PL/SQL triggers in their compiled form, just like stored
procedures. When a CREATE TRIGGER statement commits, the compiled PL/SQL code,
called P code (for pseudocode), is stored in the database and the source code
of the trigger is flushed from the shared pool.

Execution of Triggers

Oracle executes a trigger internally using the same steps used for
procedure execution. The only subtle difference is that a user has the right to
fire a trigger if he or she has the privilege to execute the triggering
statement. Other than this, triggers are validated and executed the same way as
stored procedures.

Dependency Maintenance for Triggers

Like procedures, triggers are dependent on referenced objects. Oracle
automatically manages the dependencies of a trigger on the schema objects
referenced in its trigger action. The dependency issues for triggers are the
same as those for stored procedures. Triggers are treated like stored
procedures; they are inserted into the data