9 Using Triggers

A trigger is a named PL/SQL unit that is stored in the database and executed (fired) in response to a specified event that occurs in the database.

Topics:

Overview of Triggers

A trigger is a named program unit that is stored in the database and fired (executed) in response to a specified event. The specified event is associated with either a table, a view, a schema, or the database, and it is one of the following:

  • A database manipulation (DML) statement (DELETE, INSERT, or UPDATE)

  • A database definition (DDL) statement (CREATE, ALTER, or DROP)

  • A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN)

The trigger is said to be defined on the table, view, schema, or database.

Topics:

Trigger Types

A DML trigger is fired by a DML statement, a DDL trigger is fired by a DDL statement, a DELETE trigger is fired by a DELETE statement, and so on.

An INSTEAD OF trigger is a DML trigger that is defined on a view (not a table). The database fires the INSTEAD OF trigger instead of executing the triggering DML statement. For more information, see Modifying Complex Views (INSTEAD OF Triggers).

A system trigger is defined on a schema or the database. A trigger defined on a schema fires for each event associated with the owner of the schema (the current user). A trigger defined on a database fires for each event associated with all users.

A simple trigger can fire at exactly one of the following timing points:

  • Before the triggering statement executes

  • After the triggering statement executes

  • Before each row that the triggering statement affects

  • After each row that the triggering statement affects

A compound trigger can fire at more than one timing point. Compound triggers make it easier to program an approach where you want the actions you implement for the various timing points to share common data. For more information, see Compound Triggers.

Trigger States

A trigger can be in either of two states:

Enabled. An enabled trigger executes its trigger body if a triggering statement is entered and the trigger restriction (if any) evaluates to TRUE.

Disabled. A disabled trigger does not execute its trigger body, even if a triggering statement is entered and the trigger restriction (if any) evaluates to TRUE.

By default, a trigger is created in enabled state. To create a trigger in disabled state, use the DISABLE clause of the CREATE TRIGGER statement.

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 running 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 must read (query) or write (update), then the SQL statements in the body of the trigger being fired use the following guidelines:

  • Queries see the current read-consistent materialized view of referenced tables and any data changed within the same transaction.

  • Updates wait for existing data locks to be released before proceeding.

Uses of Triggers

Triggers supplement the standard capabilities of your database to provide a highly customized database management system. For example, you can use triggers to:

  • Automatically generate derived column values

  • Enforce referential integrity across nodes in a distributed database

  • Enforce complex business rules

  • Provide transparent event logging

  • Provide 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

  • Restrict DML operations against a table to those issued during regular business hours

  • Enforce security authorizations

  • Prevent invalid transactions

Caution:

Triggers are not reliable security mechanisms, because they are programmatic and easy to disable. For high assurance security, use Oracle Database Vault. For more information, see Oracle Database Vault Administrator's Guide.

Guidelines for Designing Triggers

Use the following guidelines when designing triggers:

  • Use triggers to guarantee that when a specific operation is performed, related actions are performed.

  • Do not define triggers that duplicate database features.

    For example, do not define triggers to reject bad data if you can do the same checking through constraints.

    Although you can use both triggers and integrity constraints to define and enforce any type of integrity rule, Oracle 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

      • PRIMARY KEY

      • FOREIGN KEY

      • CHECK

      • DELETE CASCADE

      • DELETE SET NULL

  • Limit the size of triggers.

    If the logic for your trigger requires much more than 60 lines of PL/SQL code, put most of the code in a stored subprogram and invoke the subprogram from the trigger.

    The size of the trigger cannot exceed 32K.

  • Use triggers only for centralized, global operations that must fire for the triggering statement, regardless of which user or database application issues the statement.

  • Do not create recursive triggers.

    For example, if you create an AFTER UPDATE statement trigger on the employees table, and the trigger itself issues an UPDATE statement on the employees table, the trigger fires recursively until it runs out of memory.

  • Use triggers on DATABASE judiciously. They are executed for every user every time the event occurs on which the trigger is created.

  • If you use a LOGON trigger to monitor logons by users, include an exception-handling part in the trigger, and include a WHEN OTHERS exception in the exception-handling part. Otherwise, an unhandled exception might block all connections to the database.

  • If you use a LOGON trigger only to execute a package (for example, an application context-setting package), put the exception-handling part in the package instead of in the trigger.

Privileges Required to Use Triggers

To create a trigger in your schema:

  • You must have the CREATE TRIGGER system privilege

  • One of the following must be true:

    • You own the table specified in the triggering statement

    • You have the ALTER privilege for the table specified in the triggering statement

    • You have the ALTER ANY TABLE system privilege

To create a trigger in another schema, or to reference a table in another schema from a trigger in your schema:

  • You must have the CREATE ANY TRIGGER system privilege.

  • You must have the EXECUTE privilege on the referenced subprograms or packages.

To create a trigger on the database, you must have the ADMINISTER DATABASE TRIGGER privilege. If this privilege is later revoked, you can drop the trigger but not alter it.

The object privileges to the schema objects referenced in the trigger body must be granted to the trigger owner explicitly (not through a role). The statements in the trigger body operate under the privilege domain of the trigger owner, not the privilege domain of the user issuing the triggering statement (this is similar to the privilege model for stored subprograms).

Creating Triggers

To create a trigger, use the CREATE TRIGGER statement. By default, a trigger is created in enabled state. To create a trigger in disabled state, use the DISABLE clause of the CREATE TRIGGER statement. For information about trigger states, see Overview of Triggers.

When using the CREATE TRIGGER statement with an interactive tool, such as SQL*Plus or Enterprise Manager, put a single slash (/) on the last line, as in Example 9-1, which creates a simple trigger for the emp table.

Example 9-1 CREATE TRIGGER Statement

CREATE OR REPLACE TRIGGER Print_salary_changes
  BEFORE DELETE OR INSERT OR UPDATE ON emp
  FOR EACH ROW
WHEN (NEW.EMPNO > 0)
DECLARE
    sal_diff number;
BEGIN
    sal_diff  := :NEW.SAL  - :OLD.SAL;
    dbms_output.put('Old salary: ' || :OLD.sal);
    dbms_output.put('  New salary: ' || :NEW.sal);
    dbms_output.put_line('  Difference ' || sal_diff);
END;
/

The trigger in Example 9-1 fires when DML operations are performed on the table. You can choose what combination of operations must fire the trigger.

Because the trigger uses the BEFORE keyword, it can access the new values before they go into the table, and can change the values if there is an easily-corrected error by assigning to :NEW.column_name. You might use the AFTER keyword if you want the trigger to query or change the same table, because triggers can only do that after the initial changes are applied and the table is back in a consistent state.

Because the trigger uses the FOR EACH ROW clause, it might be executed multiple times, such as when updating or deleting multiple rows. You might omit this clause if you just want to record the fact that the operation occurred, but not examine the data for each row.

After the trigger is created, following SQL statement fires the trigger once for each row that is updated, in each case printing the new salary, the old salary, and the difference between them:

UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10;

The CREATE (or CREATE OR REPLACE) statement fails if any errors exist in the PL/SQL block.

The following sections use Example 9-1 to show how parts of a trigger are specified. For additional examples of CREATE TRIGGER statements, see Examples of Trigger Applications.

Topics:

Naming Triggers

Trigger names must be unique with respect to other triggers in the same schema. Trigger names need not be unique with respect to other schema objects, such as tables, views, and subprograms. For example, a table and a trigger can have the same name (however, to avoid confusion, this is not recommended).

When Does the Trigger Fire?

A trigger fires based on a triggering statement, which specifies:

  • The SQL statement, database event, or DDL event that fires the trigger body. The options include DELETE, INSERT, and UPDATE. One, two, or all three of these options can be included in the triggering statement specification.

  • The table, view, DATABASE, or SCHEMA on which the trigger is defined.

    Note:

    Exactly one table or view can be specified in the triggering statement. If the INSTEAD OF option is used, then the triggering statement must specify a view; conversely, if a view is specified in the triggering statement, then only the INSTEAD OF option can be used.

In Example 9-1, the PRINT_SALARY_CHANGES trigger fires after any DELETE, INSERT, or UPDATE on the emp table. Any of the following statements trigger the PRINT_SALARY_CHANGES trigger:

DELETE FROM emp;
INSERT INTO emp VALUES ( ... );
INSERT INTO emp SELECT ... FROM ... ;
UPDATE emp SET ... ;

Do Import and SQL*Loader Fire Triggers?

INSERT triggers fire during SQL*Loader conventional loads. (For direct loads, triggers are disabled before the load.)

The IGNORE parameter of the IMP statement determines whether triggers fire during import operations:

  • If IGNORE=N (default) and the table already exists, then import does not change the table and no existing triggers fire.

  • If the table does not exist, then import creates and loads it before any triggers are defined, so again no triggers fire.

  • If IGNORE=Y, then import loads rows into existing tables. Any existing triggers fire, and indexes are updated to account for the imported data.

How Column Lists Affect UPDATE Triggers

An UPDATE statement might include a list of columns. If a triggering statement includes a column list, the trigger fires only when one of the specified columns is updated. If a triggering statement omits a column list, the trigger fires when any column of the associated table is updated. A column list cannot be specified for INSERT or DELETE triggering statements.

The previous example of the PRINT_SALARY_CHANGES trigger can include a column list in the triggering statement. For example:

... BEFORE DELETE OR INSERT OR UPDATE OF ename ON emp ...

Note:

  • You cannot specify a column list for UPDATE with INSTEAD OF triggers.

  • If the column specified in the UPDATE OF clause is an object column, then the trigger also fires if any of the attributes of the object are modified.

  • You cannot specify UPDATE OF clauses on collection columns.

Controlling When a Trigger Fires (BEFORE and AFTER Options)

Note:

This topic applies only to simple triggers. For the options of compound triggers, see Compound Triggers.

The BEFORE or AFTER option in the CREATE TRIGGER statement specifies exactly when to fire the trigger body in relation to the triggering statement that is being run. In a CREATE TRIGGER statement, the BEFORE or AFTER option is specified just before the triggering statement. For example, the PRINT_SALARY_CHANGES trigger in the previous example is a BEFORE trigger.

In general, you use BEFORE or AFTER triggers to achieve the following results:

  • Use BEFORE row triggers to modify the row before the row data is written to disk.

  • Use AFTER row triggers to obtain, and perform operations, using the row ID.

    An AFTER row trigger fires when the triggering statement results in ORA-2292.

    Note:

    BEFORE row triggers are slightly more efficient than AFTER row triggers. With AFTER row triggers, affected data blocks must be read (logical read, not physical read) once for the trigger and then again for the triggering statement. Alternatively, with BEFORE row triggers, the data blocks must be read only once for both the triggering statement and the trigger.

If an UPDATE or DELETE statement detects a conflict with a concurrent UPDATE, then the database performs a transparent ROLLBACK to SAVEPOINT and restarts the update. This can occur many times before the statement completes successfully. Each time the statement is restarted, the BEFORE statement trigger fires again. The rollback to savepoint does not undo changes to any package variables referenced in the trigger. Include a counter variable in your package to detect this situation.

Ordering of Triggers

A relational database does not guarantee the order of rows processed by a SQL statement. Therefore, do not create triggers that depend on the order in which rows are processed. For example, do not assign a value to a global package variable in a row trigger if the current value of the global variable is dependent on the row being processed by the row trigger. Also, if global package variables are updated within a trigger, then it is best to initialize those variables in a BEFORE statement trigger.

When a statement in a trigger body causes another trigger to fire, the triggers are said to be cascading. The database allows up to 32 triggers to cascade at simultaneously. You can limit the number of trigger cascades by using the initialization parameter OPEN_CURSORS, because a cursor must be opened for every execution of a trigger.

Although any trigger can run a sequence of operations either inline or by invoking subprograms, using multiple triggers of the same type allows the modular installation of applications that have triggers on the same tables.

Each subsequent trigger sees the changes made by the previously fired triggers. Each trigger can see the old and new values. The old values are the original values, and the new values are the current values, as set by the most recently fired UPDATE or INSERT trigger.

The database executes all triggers of the same type before executing triggers of a different type. If you have multiple triggers of the same type on the same table, and the order in which they execute is important, use the FOLLOWS clause. Without the FOLLOWS clause, the database chooses an arbitrary, unpredictable order.

See Also:

CREATE TRIGGER Statement for more information about ordering of triggers and the FOLLOWS clause

Modifying Complex Views (INSTEAD OF Triggers)

Note:

INSTEAD OF triggers can be defined only on views, not on tables.

An updatable view is one that lets you perform DML on the underlying table. Some views are inherently updatable, but others are not because they were created with one or more of the constructs listed in Views that Require INSTEAD OF Triggers.

Any view that contains one of those constructs can be made updatable by using an INSTEAD OF trigger. INSTEAD OF triggers provide a transparent way of modifying views that cannot be modified directly through UPDATE, INSERT, and DELETE statements. These triggers are invoked INSTEAD OF triggers because, unlike other types of triggers, the database fires the trigger instead of executing the triggering statement. The trigger must determine what operation was intended and perform UPDATE, INSERT, or DELETE operations directly on the underlying tables.

With an INSTEAD OF trigger, you can write normal UPDATE, INSERT, and DELETE statements against the view, and the INSTEAD OF trigger works invisibly in the background to make the right actions take place.

INSTEAD OF triggers can only be activated for each row.

Note:

  • The INSTEAD OF option can be used only for triggers defined on views.

  • The BEFORE and AFTER options cannot be used for triggers defined on 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.

Views that Require INSTEAD OF Triggers

A view cannot be modified by UPDATE, INSERT, or DELETE statements if the view query contains any of the following constructs:

  • A set operator

  • A DISTINCT operator

  • An aggregate or analytic function

  • A GROUP BY, ORDER BY, MODEL, CONNECT BY, or START WITH clause

  • A collection expression in a SELECT list

  • A subquery in a SELECT list

  • A subquery designated WITH READ ONLY

  • Joins, with some exceptions, as documented in Oracle Database Administrator's Guide

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

INSTEAD OF triggers provide the means to modify object view instances on the client-side through OCI calls.

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 modifiable. If the object is read only, then it is not necessary to define triggers to pin it.

Triggers on Nested Table View Columns

INSTEAD OF triggers can also be created over nested table view columns. These triggers provide a way of updating elements of the nested table. They fire for each nested table element being modified. The row correlation variables inside the trigger correspond to the nested table element. This type of trigger also provides an additional correlation name for accessing the parent row that contains the nested table being modified.

Note:

These triggers:
  • Can only be defined over nested table columns in views.

  • Fire only when the nested table elements are modified using the TABLE clause. They do not fire when a DML statement is performed on the view.

For example, consider a department view that contains a nested table of employees.

CREATE OR REPLACE VIEW Dept_view AS
  SELECT d.Deptno, d.Dept_type, d.Dname,
    CAST (MULTISET ( SELECT e.Empno, e.Empname, e.Salary)
      FROM emp e
        WHERE e.Deptno = d.Deptno) AS Amp_list_ Emplist
      FROM dept d;

The CAST (MULTISET) operator creates a multiset of employees for each department. To modify the emplist column, which is the nested table of employees, define an INSTEAD OF trigger over the column to handle the operation.

The following example shows how an insert trigger might be written:

CREATE OR REPLACE TRIGGER Dept_emplist_tr
  INSTEAD OF INSERT ON NESTED TABLE Emplist OF Dept_view
    REFERENCING NEW AS Employee
      PARENT AS Department
        FOR EACH ROW
BEGIN
  -- Insert on nested table translates to insert on base table:
  INSERT INTO emp VALUES (:Employee.Empno,
    :Employee.Ename,:Employee.Sal, :Department.Deptno);
END;

Any INSERT into the nested table fires the trigger, and the emp table is filled with the correct values. For example:

INSERT INTO TABLE (SELECT d.Emplist FROM Dept_view d WHERE Deptno = 10)
  VALUES (1001, 'John Glenn', 10000);

The :department.deptno correlation variable in this example has the value 10.

Example: INSTEAD OF Trigger

Note:

You might need to set up the following data structures for this example to work:
CREATE TABLE Project_tab (
  Prj_level NUMBER,
  Projno    NUMBER,
  Resp_dept NUMBER);
CREATE TABLE emp (
  Empno     NUMBER NOT NULL,
  Ename     VARCHAR2(10),
  Job       VARCHAR2(9),
  Mgr       NUMBER(4),
  Hiredate  DATE,
  Sal       NUMBER(7,2),
  Comm      NUMBER(7,2),
  Deptno    NUMBER(2) NOT NULL);

CREATE TABLE dept (
  Deptno    NUMBER(2) NOT NULL,
  Dname     VARCHAR2(14),
  Loc       VARCHAR2(13),
  Mgr_no    NUMBER,
  Dept_type NUMBER);

The following example shows an INSTEAD OF trigger for inserting rows into the MANAGER_INFO view.

CREATE OR REPLACE VIEW manager_info AS
  SELECT e.ename, e.empno, d.dept_type, d.deptno, p.prj_level,  p.projno
    FROM emp e, dept d, Project_tab p
      WHERE e.empno =  d.mgr_no
        AND d.deptno = p.resp_dept;

CREATE OR REPLACE TRIGGER manager_info_insert
  INSTEAD OF INSERT ON manager_info
    REFERENCING NEW AS n  -- new manager information
      FOR EACH ROW
DECLARE
  rowcnt number;
BEGIN
  SELECT COUNT(*) INTO rowcnt FROM emp WHERE empno = :n.empno;
  IF rowcnt = 0  THEN
    INSERT INTO emp (empno,ename) VALUES (:n.empno, :n.ename);
  ELSE
    UPDATE emp SET emp.ename = :n.ename WHERE emp.empno = :n.empno;
  END IF;
  SELECT COUNT(*) INTO rowcnt FROM dept WHERE deptno = :n.deptno;
  IF rowcnt = 0 THEN
    INSERT INTO dept (deptno, dept_type) 
      VALUES(:n.deptno, :n.dept_type);
  ELSE
    UPDATE dept SET dept.dept_type = :n.dept_type
      WHERE dept.deptno = :n.deptno;
  END IF;
  SELECT COUNT(*) INTO rowcnt FROM Project_tab
    WHERE Project_tab.projno = :n.projno;
  IF rowcnt = 0 THEN
    INSERT INTO Project_tab (projno, prj_level) 
      VALUES(:n.projno, :n.prj_level);
  ELSE
    UPDATE Project_tab SET Project_tab.prj_level = :n.prj_level
      WHERE Project_tab.projno = :n.projno;
  END IF;
END;

The actions shown for rows being inserted into the MANAGER_INFO view first test to see if appropriate rows already exist in the base tables from which MANAGER_INFO is derived. The actions then insert new rows or update existing rows, as appropriate. Similar triggers can specify appropriate actions for UPDATE and DELETE.

Firing Triggers One or Many Times (FOR EACH ROW Option)

Note:

This topic applies only to simple triggers. For the options of compound triggers, see Compound Triggers.

The FOR EACH ROW option determines whether the trigger is a row trigger or a statement trigger. If you specify FOR EACH ROW, then the trigger fires once for each row of the table that is affected by the triggering statement. The absence of the FOR EACH ROW option indicates that the trigger fires only once for each applicable statement, but not separately for each row affected by the statement.

For example, assume that the table Emp_log was created as follows:

CREATE TABLE Emp_log (
  Emp_id     NUMBER,
  Log_date   DATE,
  New_salary NUMBER,
  Action     VARCHAR2(20));

Then, define the following trigger:

CREATE OR REPLACE TRIGGER Log_salary_increase
  AFTER UPDATE ON emp
    FOR EACH ROW
      WHEN (NEW.Sal > 1000)
BEGIN
  INSERT INTO Emp_log (Emp_id, Log_date, New_salary, Action)
    VALUES (:NEW.Empno, SYSDATE, :NEW.SAL, 'NEW SAL');
END;

Then, you enter the following SQL statement:

UPDATE emp SET Sal = Sal + 1000.0
   WHERE Deptno = 20;

If there are five employees in department 20, then the trigger fires five times when this statement is entered, because five rows are affected.

The following trigger fires only once for each UPDATE of the emp table:

CREATE OR REPLACE TRIGGER Log_emp_update
  AFTER UPDATE ON emp
BEGIN
  INSERT INTO Emp_log (Log_date, Action)
    VALUES (SYSDATE, 'emp COMMISSIONS CHANGED');
END;

The statement level triggers are useful for performing validation checks for the entire statement.

Firing Triggers Based on Conditions (WHEN Clause)

Optionally, a trigger restriction can be included in the definition of a row trigger by specifying a Boolean SQL expression in a WHEN clause.

Note:

A WHEN clause cannot be included in the definition of a statement trigger.

If included, then the expression in the WHEN clause is evaluated for each row that the trigger affects.

If the expression evaluates to TRUE for a row, then the trigger body executes on behalf of that row. However, if the expression evaluates to FALSE or NOT TRUE for a row (unknown, as with nulls), then the trigger body does not execute for that row. The evaluation of the WHEN clause does not have an effect on the execution of the triggering SQL statement (in other words, the triggering statement is not rolled back if the expression in a WHEN clause evaluates to FALSE).

For example, in the PRINT_SALARY_CHANGES trigger, the trigger body is not run if the new value of Empno is zero, NULL, or negative. In more realistic examples, you might test if one column value is less than another.

The expression in a WHEN clause of a row trigger can include correlation names, which are explained later. The expression in a WHEN clause must be a SQL expression, and it cannot include a subquery. You cannot use a PL/SQL expression (including user-defined functions) in the WHEN clause.

Note:

You cannot specify the WHEN clause for INSTEAD OF triggers.

Compound Triggers

A compound trigger can fire at more than one timing point.

Topics:

Why Use Compound Triggers?

The compound trigger makes it easier to program an approach where you want the actions you implement for the various timing points to share common data. To achieve the same effect with simple triggers, you had to model the common state with an ancillary package. This approach was both cumbersome to program and subject to memory leak when the triggering statement caused an error and the after-statement trigger did not fire.

A compound trigger has an optional declarative part and a section for each of its timing points (see Example 9-2). All of these sections can access a common PL/SQL state. The common state is established when the triggering statement starts and is destroyed when the triggering statement completes, even when the triggering statement causes an error.

Example 9-2 Compound Trigger

SQL> CREATE OR REPLACE TRIGGER compound_trigger
  2    FOR UPDATE OF salary ON employees
  3      COMPOUND TRIGGER
  4  
  5    -- Declarative part (optional)
  6    -- Variables declared here have firing-statement duration.
  7    threshold CONSTANT SIMPLE_INTEGER := 200;
  8  
  9    BEFORE STATEMENT IS
 10    BEGIN
 11      NULL;
 12    END BEFORE STATEMENT;
 13  
 14    BEFORE EACH ROW IS
 15    BEGIN
 16      NULL;
 17    END BEFORE EACH ROW;
 18  
 19    AFTER EACH ROW IS
 20    BEGIN
 21      NULL;
 22    END AFTER EACH ROW;
 23  
 24    AFTER STATEMENT IS
 25    BEGIN
 26      NULL;
 27    END AFTER STATEMENT;
 28  END compound_trigger;
 29  /
 
Trigger created.
 
SQL>

Two common reasons to use compound triggers are:

Compound Trigger Sections

A compound trigger has a declarative part and at least one timing-point section. It cannot have multiple sections for the same timing point.

The optional declarative part (the first part) declares variables and subprograms that timing-point sections can use. When the trigger fires, the declarative part executes before any timing-point sections execute. Variables and subprograms declared in this section have firing-statement duration.

A compound trigger defined on a view has an INSTEAD OF EACH ROW timing-point section, and no other timing-point section.

A compound trigger defined on a table has one or more of the timing-point sections described in Table 9-1. Timing-point sections must appear in the order shown in Table 9-1. If a timing-point section is absent, nothing happens at its timing point.

A timing-point section cannot be enclosed in a PL/SQL block.

Table 9-1 summarizes the timing point sections of a compound trigger that can be defined on a table.

Table 9-1 Timing-Point Sections of a Compound Trigger Defined

Timing Point Section

Before the triggering statement executes

BEFORE STATEMENT

After the triggering statement executes

AFTER STATEMENT

Before each row that the triggering statement affects

BEFORE EACH ROW

After each row that the triggering statement affects

AFTER EACH ROW


Any section can include the functions Inserting, Updating, Deleting, and Applying.

See Also:

CREATE TRIGGER Statement for more information about the syntax of compound triggers

Triggering Statements of Compound Triggers

The triggering statement of a compound trigger must be a DML statement.

If the triggering statement affects no rows, and the compound trigger has neither a BEFORE STATEMENT section nor an AFTER STATEMENT section, the trigger never fires.

It is when the triggering statement affects many rows that a compound trigger has a performance benefit. This is why it is important to use the BULK COLLECT clause with the FORALL statement. For example, without the BULK COLLECT clause, a FORALL statement that contains an INSERT statement simply performs a single-row insertion operation many times, and you get no benefit from using a compound trigger. For more information about using the BULK COLLECT clause with the FORALL statement, see Using FORALL and BULK COLLECT Together.

If the triggering statement of a compound trigger is an INSERT statement that includes a subquery, the compound trigger retains some of its performance benefit. For example, suppose that a compound trigger is triggered by the following statement:

INSERT INTO Target
  SELECT c1, c2, c3
    FROM Source
      WHERE Source.c1 > 0

For each row of Source whose column c1 is greater than zero, the BEFORE EACH ROW and AFTER EACH ROW sections of the compound trigger execute. However, the BEFORE STATEMENT and AFTER STATEMENT sections each execute only once (before and after the INSERT statement executes, respectively).

Compound Trigger Restrictions

  • The body of a compound trigger must be a compound trigger block.

  • A compound trigger must be a DML trigger.

  • A compound trigger must be defined on either a table or a view.

  • The declarative part cannot include PRAGMA AUTONOMOUS_TRANSACTION.

  • A compound trigger body cannot have an initialization block; therefore, it cannot have an exception section.

    This is not a problem, because the BEFORE STATEMENT section always executes exactly once before any other timing-point section executes.

  • An exception that occurs in one section must be handled in that section. It cannot transfer control to another section.

  • If a section includes a GOTO statement, the target of the GOTO statement must be in the same section.

  • :OLD, :NEW, and :PARENT cannot appear in the declarative part, the BEFORE STATEMENT section, or the AFTER STATEMENT section.

  • Only the BEFORE EACH ROW section can change the value of :NEW.

  • If, after the compound trigger fires, the triggering statement rolls back due to a DML exception:

    • Local variables declared in the compound trigger sections are re-initialized, and any values computed thus far are lost.

    • Side effects from firing the compound trigger are not rolled back.

  • The firing order of compound triggers is not guaranteed. Their firing can be interleaved with the firing of simple triggers.

  • If compound triggers are ordered using the FOLLOWS option, and if the target of FOLLOWS does not contain the corresponding section as source code, the ordering is ignored.

Compound Trigger Example

Scenario: You want to record every change to hr.employees.salary in a new table, employee_salaries. A single UPDATE statement will update many rows of the table hr.employees; therefore, bulk-inserting rows into employee.salaries is more efficient than inserting them individually.

Solution: Define a compound trigger on updates of the table hr.employees, as in Example 9-3. You do not need a BEFORE STATEMENT section to initialize idx or salaries, because they are state variables, which are initialized each time the trigger fires (even when the triggering statement is interrupted and restarted).

Example 9-3 Compound Trigger Records Changes to One Table in Another Table

CREATE TABLE employee_salaries (
  employee_id NUMBER NOT NULL,
  change_date DATE   NOT NULL,
  salary NUMBER(8,2) NOT NULL,
  CONSTRAINT pk_employee_salaries PRIMARY KEY (employee_id, change_date),
  CONSTRAINT fk_employee_salaries FOREIGN KEY (employee_id)
    REFERENCES employees (employee_id)
      ON DELETE CASCADE)
/
CREATE OR REPLACE TRIGGER maintain_employee_salaries
  FOR UPDATE OF salary ON employees
    COMPOUND TRIGGER

-- Declarative Part:
-- Choose small threshhold value to show how example works:
  threshhold CONSTANT SIMPLE_INTEGER := 7;

  TYPE salaries_t IS TABLE OF employee_salaries%ROWTYPE INDEX BY SIMPLE_INTEGER;
  salaries  salaries_t;
  idx       SIMPLE_INTEGER := 0;

  PROCEDURE flush_array IS
    n CONSTANT SIMPLE_INTEGER := salaries.count();
  BEGIN
    FORALL j IN 1..n
      INSERT INTO employee_salaries VALUES salaries(j);
    salaries.delete();
    idx := 0;
    DBMS_OUTPUT.PUT_LINE('Flushed ' || n || ' rows');
  END flush_array;

  -- AFTER EACH ROW Section:

  AFTER EACH ROW IS
  BEGIN
    idx := idx + 1;
    salaries(idx).employee_id := :NEW.employee_id;
    salaries(idx).change_date := SYSDATE();
    salaries(idx).salary := :NEW.salary;
    IF idx >= threshhold THEN
      flush_array();
    END IF;
  END AFTER EACH ROW;

  -- AFTER STATEMENT Section:

  AFTER STATEMENT IS
  BEGIN
    flush_array();
  END AFTER STATEMENT;
END maintain_employee_salaries;
/
/* Increase salary of every employee in department 50 by 10%: */

UPDATE employees
  SET salary = salary * 1.1
  WHERE department_id = 50
/

/* Wait two seconds: */

BEGIN
  DBMS_LOCK.SLEEP(2);
END;
/

/* Increase salary of every employee in department 50 by 5%: */

UPDATE employees
  SET salary = salary * 1.05
  WHERE department_id = 50
/

Using Compound Triggers to Avoid Mutating-Table Error

You can use compound triggers to avoid the mutating-table error (ORA-04091) described in Trigger Restrictions on Mutating Tables.

Scenario: A business rule states that an employee's salary increase must not exceed 10% of the average salary for the employee's department. This rule must be enforced by a trigger.

Solution: Define a compound trigger on updates of the table hr.employees, as in Example 9-4. The state variables are initialized each time the trigger fires (even when the triggering statement is interrupted and restarted).

Example 9-4 Compound Trigger that Avoids Mutating-Table Error

CREATE OR REPLACE TRIGGER Check_Employee_Salary_Raise
  FOR UPDATE OF Salary ON Employees
COMPOUND TRIGGER
  Ten_Percent                 CONSTANT NUMBER := 0.1;
  TYPE Salaries_t             IS TABLE OF Employees.Salary%TYPE;
  Avg_Salaries                Salaries_t;
  TYPE Department_IDs_t       IS TABLE OF Employees.Department_ID%TYPE;
  Department_IDs              Department_IDs_t;

  TYPE Department_Salaries_t  IS TABLE OF Employees.Salary%TYPE
                                INDEX BY VARCHAR2(80);
  Department_Avg_Salaries     Department_Salaries_t;

  BEFORE STATEMENT IS
  BEGIN
    SELECT               AVG(e.Salary), NVL(e.Department_ID, -1)
      BULK COLLECT INTO  Avg_Salaries, Department_IDs
      FROM               Employees e
      GROUP BY           e.Department_ID;
    FOR j IN 1..Department_IDs.COUNT() LOOP
      Department_Avg_Salaries(Department_IDs(j)) := Avg_Salaries(j);
    END LOOP;
  END BEFORE STATEMENT;

  AFTER EACH ROW IS
  BEGIN
    IF :NEW.Salary - :Old.Salary >
      Ten_Percent*Department_Avg_Salaries(:NEW.Department_ID)
    THEN
      Raise_Application_Error(-20000, 'Raise too big');
    END IF;
  END AFTER EACH ROW;
END Check_Employee_Salary_Raise;

Coding the Trigger Body

Note:

This topic applies primarily to simple triggers. The body of a compound trigger has a different format (see Compound Triggers).

The trigger body is either a CALL subprogram (a PL/SQL subprogram, or a Java subprogram encapsulated in a PL/SQL wrapper) or a PL/SQL block, and as such, it can include SQL and PL/SQL statements. These statements are executed if the triggering statement is entered and if the trigger restriction (if any) evaluates to TRUE.

If the trigger body for a row trigger is a PL/SQL block (not a CALL subprogram), it can include the following constructs:

  • REFERENCING clause, which can specify correlation names OLD, NEW, and PARENT

  • Conditional predicates INSERTING, DELETING, and UPDATING

See Also:

CREATE TRIGGER Statement for syntax and semantics of this statement

The LOGON trigger in Example 9-5 executes the procedure sec_mgr.check_user after a user logs onto the database. The body of the trigger includes an exception-handling part, which includes a WHEN OTHERS exception that invokes RAISE_APPLICATION_ERROR.

Example 9-5 Monitoring Logons with a Trigger

CREATE OR REPLACE TRIGGER check_user
  AFTER LOGON ON DATABASE
  BEGIN
    sec_mgr.check_user;
  EXCEPTION
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR
        (-20000, 'Unexpected error: '|| DBMS_Utility.Format_Error_Stack);
 END;
/

Although triggers are declared using PL/SQL, they can call subprograms in other languages. The trigger in Example 9-6 invokes a Java subprogram.

Example 9-6 Invoking a Java Subprogram from a Trigger

CREATE OR REPLACE PROCEDURE Before_delete (Id IN NUMBER, Ename VARCHAR2)
IS language Java
name 'thjvTriggers.beforeDelete (oracle.sql.NUMBER, oracle.sql.CHAR)';

CREATE OR REPLACE TRIGGER Pre_del_trigger BEFORE DELETE ON Tab 
FOR EACH ROW
CALL Before_delete (:OLD.Id, :OLD.Ename)
/

The corresponding Java file is thjvTriggers.java:

import java.sql.*
import java.io.*
import oracle.sql.*
import oracle.oracore.*
public class thjvTriggers
{
public state void
beforeDelete (NUMBER old_id, CHAR old_name)
Throws SQLException, CoreException
   {
   Connection conn = JDBCConnection.defaultConnection();
   Statement stmt = conn.CreateStatement();
   String sql = "insert into logtab values
   ("+ old_id.intValue() +", '"+ old_ename.toString() + ", BEFORE DELETE');
   stmt.executeUpdate (sql);
   stmt.close();
   return;
   }
}

Topics:

Accessing Column Values in Row Triggers

Within a trigger body of a row trigger, the PL/SQL code and SQL statements have access to the old and new column values of the current row affected by the triggering statement. Two correlation names exist for every column of the table being modified: one for the old column value, and one for the new column value. Depending on the type of triggering statement, certain correlation names might not have any meaning.

  • A trigger fired by an INSERT statement has meaningful access to new column values only. Because the row is being created by the INSERT, the old values are null.

  • A trigger fired by an UPDATE statement has access to both old and new column values for both BEFORE and AFTER row triggers.

  • A trigger fired by a DELETE statement has meaningful access to :OLD column values only. Because the row no longer exists after the row is deleted, the :NEW values are NULL. However, you cannot modify :NEW values because ORA-4084 is raised if you try to modify :NEW values.

The new column values are referenced using the NEW qualifier before the column name, while the old column values are referenced using the OLD qualifier before the column name. For example, if the triggering statement is associated with the emp table (with the columns SAL, COMM, and so on), then you can include statements in the trigger body. For example:

IF :NEW.Sal > 10000 ...
IF :NEW.Sal < :OLD.Sal ...

Old and new values are available in both BEFORE and AFTER row triggers. A NEW column value can be assigned in a BEFORE row trigger, but not in an AFTER row trigger (because the triggering statement takes effect before an AFTER row trigger fires). If a BEFORE row trigger changes the value of NEW.column, then an AFTER row trigger fired by the same statement sees the change assigned by the BEFORE row trigger.

Correlation names can also be used in the Boolean expression of a WHEN clause. A colon (:) must precede the OLD and NEW qualifiers when they are used in a trigger body, but a colon is not allowed when using the qualifiers in the WHEN clause or the REFERENCING option.

Example: Modifying LOB Columns with a Trigger

You can treat LOB columns the same as other columns, using regular SQL and PL/SQL functions with CLOB columns, and calls to the DBMS_LOB package with BLOB columns:

drop table tab1;

create table tab1 (c1 clob);
insert into tab1 values ('<h1>HTML Document Fragment</h1><p>Some text.');

create or replace trigger trg1
  before update on tab1
  for each row
begin
  dbms_output.put_line('Old value of CLOB column: '||:OLD.c1);
  dbms_output.put_line('Proposed new value of CLOB column: '||:NEW.c1);

-- Previously, you couldn't change the new value for a LOB.
-- Now, you can replace it, or construct a new value using SUBSTR, INSTR...
-- operations for a CLOB, or DBMS_LOB calls for a BLOB.
  :NEW.c1 := :NEW.c1 || to_clob('<hr><p>Standard footer paragraph.');

  dbms_output.put_line('Final value of CLOB column: '||:NEW.c1);
end;
/ 

set serveroutput on;
update tab1 set c1 = '<h1>Different Document Fragment</h1><p>Different text.';

select * from tab1;

INSTEAD OF Triggers on Nested Table View Columns

In the case of INSTEAD OF triggers on nested table view columns, the NEW and OLD qualifiers correspond to the new and old nested table elements. The parent row corresponding to this nested table element can be accessed using the parent qualifier. The parent correlation name is meaningful and valid only inside a nested table trigger.

Avoiding Trigger Name Conflicts (REFERENCING Option)

The REFERENCING option can be specified in a trigger body of a row trigger to avoid name conflicts among the correlation names and tables that might be named OLD or NEW. Because this is rare, this option is infrequently used.

For example, assume that the table new was created as follows:

CREATE TABLE new (
   field1     NUMBER, 
   field2     VARCHAR2(20));

The following CREATE TRIGGER example shows a trigger defined on the new table that can use correlation names and avoid naming conflicts between the correlation names and the table name:

CREATE OR REPLACE TRIGGER Print_salary_changes
BEFORE UPDATE ON new
REFERENCING new AS Newest
FOR EACH ROW
BEGIN
   :Newest.Field2 := TO_CHAR (:newest.field1);
END;

Notice that the NEW qualifier is renamed to newest using the REFERENCING option, and it is then used in the trigger body.

Detecting the DML Operation that Fired a Trigger

If more than one type of DML operation can fire a trigger (for example, ON INSERT OR DELETE OR UPDATE OF emp), the trigger body can use the conditional predicates INSERTING, DELETING, and UPDATING to check which type of statement fire the trigger.

Within the code of the trigger body, you can execute blocks of code depending on the kind of DML operation that fired the trigger:

IF INSERTING THEN ... END IF;
IF UPDATING THEN ... END IF;

The first condition evaluates to TRUE only if the statement that fired the trigger is an INSERT statement; the second condition evaluates to TRUE only if the statement that fired the trigger is an UPDATE statement.

In an UPDATE trigger, a column name can be specified with an UPDATING conditional predicate to determine if the named column is being updated. For example, assume a trigger is defined as the following:

CREATE OR REPLACE TRIGGER ...
... UPDATE OF Sal, Comm ON emp ...
BEGIN

... IF UPDATING ('SAL') THEN ... END IF;

END;

The code in the THEN clause runs only if the triggering UPDATE statement updates the SAL column. This way, the trigger can minimize its overhead when the column of interest is not being changed.

Error Conditions and Exceptions in the Trigger Body

If a predefined or user-defined error condition (exception) is raised during the execution of a trigger body, then all effects of the trigger body, as well as the triggering statement, are rolled back (unless the error is trapped by an exception handler). Therefore, a trigger body can prevent the execution of the triggering statement by raising an exception. User-defined exceptions are commonly used in triggers that enforce complex security authorizations or constraints.

If the LOGON trigger raises an exception, logon fails except in the following cases:

  • Database startup and shutdown operations do not fail even if the system triggers for these events raise exceptions. Only the trigger action is rolled back. The error is logged in trace files and the alert log.

  • If the system trigger is a DATABASE LOGON trigger and the user has ADMINISTER DATABASE TRIGGER privilege, then the user is able to log on successfully even if the trigger raises an exception. For SCHEMA LOGON triggers, if the user logging on is the trigger owner or has ALTER ANY TRIGGER privileges then logon is permitted. Only the trigger action is rolled back and an error is logged in the trace files and alert log.

Triggers on Object Tables

You can use the OBJECT_VALUE pseudocolumn in a trigger on an object table because, as of 10g Release 1 (10.1), OBJECT_VALUE means the object as a whole. This is one example of its use. You can also invoke a PL/SQL function with OBJECT_VALUE as the data type of an IN formal parameter.

Here is an example of the use of OBJECT_VALUE in a trigger. To keep track of updates to values in an object table tbl, a history table, tbl_history, is also created in the following example. For tbl, the values 1 through 5 are inserted into n, while m is kept at 0. The trigger is a row-level trigger that executes once for each row affected by a DML statement. The trigger causes the old and new values of the object t in tbl to be written in tbl_history when tbl is updated. These old and new values are :OLD.OBJECT_VALUE and :NEW.OBJECT_VALUE. An update of the table tbl is done (each value of n is increased by 1). A select from the history table to check that the trigger works is then shown at the end of the example:

CREATE OR REPLACE TYPE t AS OBJECT (n NUMBER, m NUMBER)
/
CREATE TABLE tbl OF t
/
BEGIN
  FOR j IN 1..5 LOOP
    INSERT INTO tbl VALUES (t(j, 0));
  END LOOP;
END;
/
CREATE TABLE tbl_history ( d DATE, old_obj t, new_obj t)
/
CREATE OR REPLACE TRIGGER Tbl_Trg
AFTER UPDATE ON tbl
FOR EACH ROW
BEGIN
  INSERT INTO tbl_history (d, old_obj, new_obj)
    VALUES (SYSDATE, :OLD.OBJECT_VALUE, :NEW.OBJECT_VALUE);
END Tbl_Trg;
/
--------------------------------------------------------------------------------
 
UPDATE tbl SET tbl.n = tbl.n+1
/
BEGIN
  FOR j IN (SELECT d, old_obj, new_obj FROM tbl_history) LOOP
    Dbms_Output.Put_Line (
      j.d||
      ' -- old: '||j.old_obj.n||' '||j.old_obj.m||
      ' -- new: '||j.new_obj.n||' '||j.new_obj.m);
  END LOOP;
END;
/

The result of the select shows that all values of column n were increased by 1. The value of m remains 0. The output of the select is:

23-MAY-05 -- old: 1 0 -- new: 2 0
23-MAY-05 -- old: 2 0 -- new: 3 0
23-MAY-05 -- old: 3 0 -- new: 4 0
23-MAY-05 -- old: 4 0 -- new: 5 0
23-MAY-05 -- old: 5 0 -- new: 6 0

Triggers and Handling Remote Exceptions

A trigger that accesses a remote site cannot do remote exception handling if the network link is unavailable. For example:

CREATE OR REPLACE TRIGGER Example
  AFTER INSERT ON emp
    FOR EACH ROW
BEGIN
  When dblink is inaccessible, compilation fails here:
  INSERT INTO emp@Remote VALUES ('x');
EXCEPTION
  WHEN OTHERS THEN
    INSERT INTO Emp_log VALUES ('x');
END;

A trigger is compiled when it is created. Thus, if a remote site is unavailable when the trigger must compile, then the database cannot validate the statement accessing the remote database, and the compilation fails. The previous example exception statement cannot run, because the trigger does not complete compilation.

Because stored subprograms are stored in a compiled form, the work-around for the previous example is as follows:

CREATE OR REPLACE TRIGGER Example
  AFTER INSERT ON emp
    FOR EACH ROW
BEGIN
  Insert_row_proc;
END;

CREATE OR REPLACE PROCEDURE Insert_row_proc AS
BEGIN
  INSERT INTO emp@Remote VALUES ('x');
EXCEPTION
  WHEN OTHERS THEN
  INSERT INTO Emp_log VALUES ('x');
END;

The trigger in this example compiles successfully and invokes the stored subprogram, which already has a validated statement for accessing the remote database; thus, when the remote INSERT statement fails because the link is down, the exception is caught.

Restrictions on Creating Triggers

Coding triggers requires some restrictions that are not required for standard PL/SQL blocks.

Topics:

Maximum Trigger Size

The size of a trigger cannot be more than 32K.

SQL Statements Allowed in Trigger Bodies

A trigger body can contain SELECT INTO statements, SELECT statements in cursor definitions, and all other DML statements.

A system trigger body can contain the DDL statements CREATETABLE, ALTERTABLE, DROP TABLE and ALTER COMPILE. A nonsystem trigger body cannot contain DDL or transaction control statements.

Note:

A subprogram invoked by a trigger cannot run the previous transaction control statements, because the subprogram runs within the context of the trigger body.

Statements inside a trigger can reference remote schema objects. However, pay special attention when invoking remote subprograms from within a local trigger. If a timestamp or signature mismatch is found during execution of the trigger, then the remote subprogram is not run, and the trigger is invalidated.

Trigger Restrictions on LONG and LONG RAW Data Types

LONG and LONG RAW data types in triggers are subject to the following restrictions:

  • A SQL statement within a trigger can insert data into a column of LONG or LONG RAW data type.

  • If data from a LONG or LONG RAW column can be converted to a constrained data type (such as CHAR and VARCHAR2), then a LONG or LONG RAW column can be referenced in a SQL statement within a trigger. The maximum length for these data types is 32000 bytes.

  • Variables cannot be declared using the LONG or LONG RAW data types.

  • :NEW and :PARENT cannot be used with LONG or LONG RAW columns.

Trigger Restrictions on Mutating Tables

A mutating table is a table that is being modified by an UPDATE, DELETE, or INSERT statement, or a table that might be updated by the effects of a DELETE CASCADE constraint.

The session that issued the triggering statement cannot query or modify a mutating table. This restriction prevents a trigger from seeing an inconsistent set of data.

This restriction applies to all triggers that use the FOR EACH ROW clause. Views being modified in INSTEAD OF triggers are not considered mutating.

When a trigger encounters a mutating table, a run-time error occurs, the effects of the trigger body and triggering statement are rolled back, and control is returned to the user or application. (You can use compound triggers to avoid the mutating-table error. For more information, see Using Compound Triggers to Avoid Mutating-Table Error.)

Consider the following trigger:

CREATE OR REPLACE TRIGGER Emp_count
  AFTER DELETE ON emp
    FOR EACH ROW
DECLARE
  n INTEGER;
BEGIN
  SELECT COUNT(*) INTO n FROM emp;
  DBMS_OUTPUT.PUT_LINE('There are now ' || n || ' employees.');
END;

If the following SQL statement is entered:

DELETE FROM emp WHERE empno = 7499;

An error is returned because the table is mutating when the row is deleted:

ORA-04091: table HR.emp is mutating, trigger/function might not see it

If you delete the line "FOR EACH ROW" from the trigger, it becomes a statement trigger that is not subject to this restriction, and the trigger.

If you must update a mutating table, you can bypass these restrictions by using a temporary table, a PL/SQL table, or a package variable. For example, in place of a single AFTER row trigger that updates the original table, resulting in a mutating table error, you might use two triggers—an AFTER row trigger that updates a temporary table, and an AFTER statement trigger that updates the original table with the values from the temporary table.

Declarative constraints are checked at various times with respect to row triggers.

See Also:

Oracle Database Concepts for information about the interaction of triggers and constraints

Because declarative referential constraints are not supported between tables on different nodes of a distributed database, the mutating table restrictions do not apply to triggers that access remote nodes. These restrictions are also not enforced among tables in the same database that are connected by loop-back database links. A loop-back database link makes a local table appear remote by defining an Oracle Net path back to the database that contains the link.

Restrictions on Mutating Tables Relaxed

The mutating error described in Trigger Restrictions on Mutating Tables prevents the trigger from reading or modifying the table that the parent statement is modifying. However, as of Oracle Database Release 8.1, a deletion from the parent table causes BEFORE and AFTER triggers to fire once. Therefore, you can create triggers (just not row triggers) to read and modify the parent and child tables.

This allows most foreign key constraint actions to be implemented through their obvious after-row trigger, providing the constraint is not self-referential. Update cascade, update set null, update set default, delete set default, inserting a missing parent, and maintaining a count of children can all be implemented easily. For example, this is an implementation of update cascade:

CREATE TABLE p (p1 NUMBER CONSTRAINT pk_p_p1 PRIMARY KEY);
CREATE TABLE f (f1 NUMBER CONSTRAINT fk_f_f1 REFERENCES p);
CREATE TRIGGER pt AFTER UPDATE ON p FOR EACH ROW BEGIN
  UPDATE f SET f1 = :NEW.p1 WHERE f1 = :OLD.p1; 
END;
/

This implementation requires care for multiple-row updates. For example, if table p has three rows with the values (1), (2), (3), and table f also has three rows with the values (1), (2), (3), then the following statement updates p correctly but causes problems when the trigger updates f:

UPDATE p SET p1 = p1+1; 

The statement first updates (1) to (2) in p, and the trigger updates (1) to (2) in f, leaving two rows of value (2) in f. Then the statement updates (2) to (3) in p, and the trigger updates both rows of value (2) to (3) in f. Finally, the statement updates (3) to (4) in p, and the trigger updates all three rows in f from (3) to (4). The relationship of the data in p and f is lost.

To avoid this problem, either forbid multiple-row updates to p that change the primary key and reuse existing primary key values, or track updates to foreign key values and modify the trigger to ensure that no row is updated twice.

That is the only problem with this technique for foreign key updates. The trigger cannot miss rows that were changed but not committed by another transaction, because the foreign key constraint guarantees that no matching foreign key rows are locked before the after-row trigger is invoked.

System Trigger Restrictions

Depending on the event, different event attribute functions are available. For example, certain DDL operations might not be allowed on DDL events. Check Event Attribute Functions before using an event attribute function, because its effects might be undefined rather than producing an error condition.

Only committed triggers fire. For example, if you create a trigger that fires after all CREATE events, then the trigger itself does not fire after the creation, because the correct information about this trigger was not committed at the time when the trigger on CREATE events fired.

For example, if you execute the following SQL statement:

CREATE OR REPLACE TRIGGER my_trigger AFTER CREATE ON DATABASE 
BEGIN null; 
END; 

Then, trigger my_trigger does not fire after the creation of my_trigger. The database does not fire a trigger that is not committed.

Foreign Function Callouts

All restrictions on foreign function callouts also apply.

Who Uses the Trigger?

The following statement, inside a trigger, returns the owner of the trigger, not the name of user who is updating the table:

SELECT Username FROM USER_USERS;

Compiling Triggers

An important difference between triggers and PL/SQL anonymous blocks is their compilation. An anonymous block is compiled each time it is loaded into memory, and its compilation has three stages:

  1. Syntax checking: PL/SQL syntax is checked, and a parse tree is generated.

  2. Semantic checking: Type checking and further processing on the parse tree.

  3. Code generation

A trigger is fully compiled when the CREATE TRIGGER statement executes. The trigger code is stored in the data dictionary. Therefore, it is unnecessary to open a shared cursor in order to execute the trigger; the trigger executes directly.

If an error occurs during the compilation of a trigger, the trigger is still created. Therefore, if a DML statement fires the trigger, the DML statement fails (unless the trigger was created in the disabled state). To see trigger compilation errors, either use the SHOW ERRORS statement in SQL*Plus or Enterprise Manager, or SELECT the errors from the USER_ERRORS view.

Topics:

Dependencies for Triggers

Compiled triggers have dependencies. They become invalid if a depended-on object, such as a stored subprogram invoked from the trigger body, is modified. Triggers that are invalidated for dependency reasons are recompiled when next invoked.

You can examine the ALL_DEPENDENCIES view to see the dependencies for a trigger. For example, the following statement shows the dependencies for the triggers in the HR schema:

SELECT NAME, REFERENCED_OWNER, REFERENCED_NAME, REFERENCED_TYPE
    FROM ALL_DEPENDENCIES
    WHERE OWNER = 'HR' and TYPE = 'TRIGGER';

Triggers might depend on other functions or packages. If the function or package specified in the trigger is dropped, then the trigger is marked invalid. An attempt is made to validate the trigger on occurrence of the event. If the trigger cannot be validated successfully, then it is marked VALID WITH ERRORS, and the event fails. For more information about dependencies between schema objects, see Oracle Database Concepts.

Note:

  • There is an exception for STARTUP events: STARTUP events succeed even if the trigger fails. There are also exceptions for SHUTDOWN events and for LOGON events if you login as SYSTEM.

  • Because the DBMS_AQ package is used to enqueue a message, dependency between triggers and queues cannot be maintained.

Recompiling Triggers

Use the ALTER TRIGGER statement to recompile a trigger manually. For example, the following statement recompiles the PRINT_SALARY_CHANGES trigger:

ALTER TRIGGER Print_salary_changes COMPILE;

To recompile a trigger, you must own the trigger or have the ALTER ANY TRIGGER system privilege.

Modifying Triggers

Like a stored subprogram, a trigger cannot be explicitly altered: It must be replaced with a new definition. (The ALTER TRIGGER statement is used only to recompile, enable, or disable a trigger.)

When replacing a trigger, you must include the OR REPLACE option in the CREATE TRIGGER statement. The OR REPLACE option is provided to allow a new version of an existing trigger to replace the older version, without affecting any grants made for the original version of the trigger.

Alternatively, the trigger can be dropped using the DROP TRIGGER statement, and you can rerun the CREATE TRIGGER statement.

To drop a trigger, the trigger must be in your schema, or you must have the DROP ANY TRIGGER system privilege.

Debugging Triggers

You can debug a trigger using the same facilities available for stored subprograms. See Oracle Database Advanced Application Developer's Guide.

Enabling Triggers

To enable a disabled trigger, use the ALTER TRIGGER statement with the ENABLE clause. For example, to enable the disabled trigger named Reorder, enter the following statement:

ALTER TRIGGER Reorder ENABLE;

To enable all triggers defined for a specific table, use the ALTER TABLE statement with the ENABLE clause and the ALL TRIGGERS option. For example, to enable all triggers defined for the Inventory table, enter the following statement:

ALTER TABLE Inventory ENABLE ALL TRIGGERS;

Disabling Triggers

You might temporarily disable a trigger if:

  • An object it references is not available.

  • You must perform a large data load, and you want it to proceed quickly without firing triggers.

  • You are reloading data.

To disable a trigger, use the ALTER TRIGGER statement with the DISABLE option. For example, to disable the trigger named Reorder, enter the following statement:

ALTER TRIGGER Reorder DISABLE;

To disable all triggers defined for a specific table, use the ALTER TABLE statement with the DISABLE clause and the ALL TRIGGERS option. For example, to disable all triggers defined for the Inventory table, enter the following statement:

ALTER TABLE Inventory DISABLE ALL TRIGGERS;

Viewing Information About Triggers

The *_TRIGGERS static data dictionary views reveal information about triggers.

The column BASE_OBJECT_TYPE specifies whether the trigger is based on DATABASE, SCHEMA, table, or view. The column TABLE_NAME is null if the base object is not table or view.

The column ACTION_TYPE specifies whether the trigger is a call type trigger or a PL/SQL trigger.

The column TRIGGER_TYPE specifies the type of the trigger; for example COMPOUND, BEFORE EVENT, or AFTER EVENT (the last two apply only to database events).

Each of the columns BEFORE_STATEMENT, BEFORE_ROW, AFTER_ROW, AFTER_STATEMENT, and INSTEAD_OF_ROW has the value YES or NO.

The column TRIGGERING_EVENT includes all system and DML events.

See Also:

Oracle Database Reference for information about *_TRIGGERS static data dictionary views

For example, assume the following statement was used to create the Reorder trigger:


CREATE OR REPLACE TRIGGER Reorder
AFTER UPDATE OF Parts_on_hand ON Inventory
FOR EACH ROW
WHEN(NEW.Parts_on_hand < NEW.Reorder_point)
DECLARE
   x NUMBER;
BEGIN
   SELECT COUNT(*) INTO x
      FROM Pending_orders
      WHERE Part_no = :NEW.Part_no;
   IF x = 0  THEN
      INSERT INTO Pending_orders
         VALUES (:NEW.Part_no, :NEW.Reorder_quantity,
                 sysdate);
   END IF;
END;

The following two queries return information about the REORDER trigger:

SELECT Trigger_type, Triggering_event, Table_name
   FROM USER_TRIGGERS
   WHERE Trigger_name = 'REORDER';

TYPE             TRIGGERING_STATEMENT       TABLE_NAME
---------------- -------------------------- ------------
AFTER EACH ROW   UPDATE                     INVENTORY

SELECT Trigger_body
   FROM USER_TRIGGERS
   WHERE Trigger_name = 'REORDER';

TRIGGER_BODY
--------------------------------------------
DECLARE
   x NUMBER;
BEGIN
   SELECT COUNT(*) INTO x
      FROM Pending_orders
      WHERE Part_no = :NEW.Part_no;
   IF x = 0
      THEN INSERT INTO Pending_orders
         VALUES (:NEW.Part_no, :NEW.Reorder_quantity,
            sysdate);
   END IF;
END;

Examples of Trigger Applications

You can use triggers in a number of ways to customize information management in the database. For example, triggers are commonly used to:

  • Provide sophisticated auditing

  • Prevent invalid transactions

  • Enforce referential integrity (either those actions not supported by declarative constraints or across nodes in a distributed database)

  • Enforce complex business rules

  • Enforce complex security authorizations

  • Provide transparent event logging

  • Automatically generate derived column values

  • Enable building complex views that are updatable

  • Track database events

This section provides an example of each of these trigger applications. These examples are not meant to be used exactly as written: They are provided to assist you in designing your own triggers.

Auditing with Triggers

Triggers are commonly used to supplement the built-in auditing features of the database. Although triggers can be written to record information similar to that recorded by the AUDIT statement, use triggers only when more detailed audit information is required. For example, use triggers to provide value-based auditing for each row.

Sometimes, the AUDIT statement is considered a security audit facility, while triggers can provide financial audit facility.

When deciding whether to create a trigger to audit database activity, consider what the database's auditing features provide, compared to auditing defined by triggers, as shown in Table 9-2.

Table 9-2 Comparison of Built-in Auditing and Trigger-Based Auditing

Audit Feature Description

DML and DDL Auditing

Standard auditing options permit auditing of DML and DDL statements regarding all types of schema objects and structures. Comparatively, triggers permit auditing of DML statements entered against tables, and DDL auditing at SCHEMA or DATABASE level.

Centralized Audit Trail

All database audit information is recorded centrally and automatically using the auditing features of the database.

Declarative Method

Auditing features enabled using the standard database features are easier to declare and maintain, and less prone to errors, when compared to auditing functions defined by triggers.

Auditing Options can be Audited

Any changes to existing auditing options can also be audited to guard against malicious database activity.

Session and Execution time Auditing

Using the database auditing features, records can be generated once every time an audited statement is entered (BY ACCESS) or once for every session that enters an audited statement (BY SESSION). Triggers cannot audit by session; an audit record is generated each time a trigger-audited table is referenced.

Auditing of Unsuccessful Data Access

Database auditing can be set to audit when unsuccessful data access occurs. However, unless autonomous transactions are used, any audit information generated by a trigger is rolled back if the triggering statement is rolled back. For more information about autonomous transactions, see Oracle Database Concepts.

Sessions can be Audited

Connections and disconnections, as well as session activity (physical I/Os, logical I/Os, deadlocks, and so on), can be recorded using standard database auditing.


When using triggers to provide sophisticated auditing, AFTER triggers are normally used. The triggering statement is subjected to any applicable constraints. If no records are found, then the AFTER trigger does not fire, and audit processing is not carried out unnecessarily.

Choosing between AFTER row and AFTER statement triggers depends on the information being audited. For example, row triggers provide value-based auditing for each table row. Triggers can also require the user to supply a "reason code" for issuing the audited SQL statement, which can be useful in both row and statement-level auditing situations.

The following example demonstrates a trigger that audits modifications to the emp table for each row. It requires that a "reason code" be stored in a global package variable before the update. This shows how triggers can be used to provide value-based auditing and how to use public package variables.

Note:

You might need to set up the following data structures for the examples to work:
CREATE OR REPLACE PACKAGE Auditpackage AS
   Reason VARCHAR2(10);
PROCEDURE Set_reason(Reason VARCHAR2);
END;
CREATE TABLE Emp99 (
   Empno               NOT NULL   NUMBER(4),
   Ename               VARCHAR2(10),
   Job                 VARCHAR2(9),
   Mgr                 NUMBER(4),
   Hiredate            DATE,
   Sal                 NUMBER(7,2),
   Comm                NUMBER(7,2),
   Deptno              NUMBER(2),
   Bonus               NUMBER,
   Ssn                 NUMBER,
   Job_classification  NUMBER);

CREATE TABLE Audit_employee (
   Oldssn              NUMBER,
   Oldname             VARCHAR2(10),
   Oldjob              VARCHAR2(2),
   Oldsal              NUMBER,
   Newssn              NUMBER,
   Newname             VARCHAR2(10),
   Newjob              VARCHAR2(2),
   Newsal              NUMBER,
   Reason              VARCHAR2(10),
   User1               VARCHAR2(10),
   Systemdate          DATE);
CREATE OR REPLACE TRIGGER Audit_employee
AFTER INSERT OR DELETE OR UPDATE ON Emp99
FOR EACH ROW
BEGIN
/* AUDITPACKAGE is a package with a public package
   variable REASON. REASON can be set by the
   application by a statement such as EXECUTE
   AUDITPACKAGE.SET_REASON(reason_string).
   A package variable has state for the duration of a
   session and that each session has a separate copy of
   all package variables. */

IF Auditpackage.Reason IS NULL THEN
   Raise_application_error(-20201, 'Must specify reason'
      || ' with AUDITPACKAGE.SET_REASON(Reason_string)');
END IF;

/* If preceding condition evaluates to TRUE,
   user-specified error number & message is raised,
   trigger stops execution, & effects of triggering statement are rolled back.
   Otherwise, new row is inserted
   into predefined auditing table named AUDIT_EMPLOYEE
   containing existing & new values of the emp table
   & reason code defined by REASON variable of AUDITPACKAGE.
   "Old" values are NULL if triggering statement is INSERT
   & "new" values are NULL if triggering statement is DELETE. */

INSERT INTO Audit_employee VALUES (
  :OLD.Ssn, :OLD.Ename, :OLD.Job_classification, :OLD.Sal,
  :NEW.Ssn, :NEW.Ename, :NEW.Job_classification, :NEW.Sal,
  auditpackage.Reason, User, Sysdate
);
END;

Optionally, you can also set the reason code back to NULL if you wanted to force the reason code to be set for every update. The following simple AFTER statement trigger sets the reason code back to NULL after the triggering statement is run:

CREATE OR REPLACE TRIGGER Audit_employee_reset
  AFTER INSERT OR DELETE OR UPDATE ON emp
BEGIN
   auditpackage.set_reason(NULL);
END;

Notice that the previous two triggers are fired by the same type of SQL statement. However, the AFTER row trigger fires once for each row of the table affected by the triggering statement, while the AFTER statement trigger fires only once after the triggering statement execution is completed.

This next trigger also uses triggers to do auditing. It tracks changes made to the emp table and stores this information in audit_table and audit_table_values.

Note:

You might need to set up the following data structures for the example to work:
CREATE TABLE audit_table (
   Seq      NUMBER,
   User_at  VARCHAR2(10),
   Time_now DATE, 
   Term     VARCHAR2(10),
   Job      VARCHAR2(10), 
   Proc     VARCHAR2(10), 
   enum     NUMBER);
CREATE SEQUENCE audit_seq;
CREATE TABLE audit_table_values (
   Seq      NUMBER, 
   Dept     NUMBER, 
   Dept1    NUMBER, 
   Dept2    NUMBER);
CREATE OR REPLACE TRIGGER Audit_emp
  AFTER INSERT OR UPDATE OR DELETE ON emp
    FOR EACH ROW
  DECLARE
    Time_now DATE;
    Terminal CHAR(10);
  BEGIN
    -- Get current time, & terminal of user:
    Time_now := SYSDATE;
    Terminal := USERENV('TERMINAL');

    -- Record new employee primary key:
    IF INSERTING THEN
      INSERT INTO audit_table VALUES (
        Audit_seq.NEXTVAL, User, Time_now,
        Terminal, 'emp', 'INSERT', :NEW.Empno
      );

      -- Record primary key of deleted row:
      ELSIF DELETING THEN
        INSERT INTO audit_table  VALUES (
          Audit_seq.NEXTVAL, User, Time_now,
          Terminal, 'emp', 'DELETE', :OLD.Empno
        );

      -- For updates, record primary key of row being updated:
      ELSE
        INSERT INTO audit_table VALUES (
          audit_seq.NEXTVAL, User, Time_now,
          Terminal, 'emp', 'UPDATE', :OLD.Empno
        );

         -- For SAL & DEPTNO, record old & new values:
         IF UPDATING ('SAL') THEN
           INSERT INTO audit_table_values VALUES (
             Audit_seq.CURRVAL, 'SAL',
             :OLD.Sal, :NEW.Sal
           );

         ELSIF UPDATING ('DEPTNO') THEN
           INSERT INTO audit_table_values VALUES (
             Audit_seq.CURRVAL, 'DEPTNO',
            :OLD.Deptno, :NEW.DEPTNO
           );
         END IF;
      END IF;
END;

Contraints and Triggers

Triggers and declarative constraints can both be used to constrain data input. However, triggers and constraints have significant differences.

Declarative constraints are statements about the database that are always true. A constraint applies to existing data in the table and any statement that manipulates the table.

Triggers constrain what a transaction can do. A trigger does not apply to data loaded before the definition of the trigger; therefore, it is not known if all data in a table conforms to the rules established by an associated trigger.

Although triggers can be written to enforce many of the same rules supported by declarative constraint features, use triggers only to enforce complex business rules that cannot be defined using standard constraints. The declarative constraint features provided with the database offer the following advantages when compared to constraints defined by triggers:

  • Centralized integrity checks

    All points of data access must adhere to the global set of rules defined by the constraints corresponding to each schema object.

  • Declarative method

    Constraints defined using the standard constraint features are much easier to write and are less prone to errors, when compared with comparable constraints defined by triggers.

While most aspects of data integrity can be defined and enforced using declarative constraints, triggers can be used to enforce complex business constraints not definable using declarative constraints. For example, triggers can be used to enforce:

  • UPDATE SET NULL, and UPDATE and DELETE SET DEFAULT referential actions.

  • Referential integrity when the parent and child tables are on different nodes of a distributed database.

  • Complex check constraints not definable using the expressions allowed in a CHECK constraint.

Referential Integrity Using Triggers

Use triggers only when performing an action for which there is no declarative support.

When using triggers to maintain referential integrity, declare the PRIMARY (or UNIQUE) KEY constraint in the parent table. If referential integrity is being maintained between a parent and child table in the same database, then you can also declare the foreign key in the child table, but disable it. Disabling the trigger in the child table prevents the corresponding PRIMARY KEY constraint from being dropped (unless the PRIMARY KEY constraint is explicitly dropped with the CASCADE option).

To maintain referential integrity using triggers:

  • For the child table, define a trigger that ensures that values inserted or updated in the foreign key correspond to values in the parent key.

  • For the parent table, define one or more triggers that ensure the desired referential action (RESTRICT, CASCADE, or SET NULL) for values in the foreign key when values in the parent key are updated or deleted. No action is required for inserts into the parent table (no dependent foreign keys exist).

The following topics provide examples of the triggers necessary to enforce referential integrity:

The examples in the following sections use the emp and dept table relationship. Several of the triggers include statements that lock rows (SELECT FOR UPDATE). This operation is necessary to maintain concurrency as the rows are being processed.

Foreign Key Trigger for Child Table

The following trigger guarantees that before an INSERT or UPDATE statement affects a foreign key value, the corresponding value exists in the parent key. The mutating table exception included in the following example allows this trigger to be used with the UPDATE_SET_DEFAULT and UPDATE_CASCADE triggers. This exception can be removed if this trigger is used alone.


CREATE OR REPLACE TRIGGER Emp_dept_check
  BEFORE INSERT OR UPDATE OF Deptno ON emp
    FOR EACH ROW WHEN (new.Deptno IS NOT NULL)

  -- Before row is inserted or DEPTNO is updated in emp table,
  -- fire this trigger to verify that new foreign key value (DEPTNO)
  -- is present in dept table.
DECLARE
  Dummy              INTEGER;  -- Use for cursor fetch
  Invalid_department EXCEPTION;
  Valid_department   EXCEPTION;
  Mutating_table     EXCEPTION;
  PRAGMA EXCEPTION_INIT (Mutating_table, -4091);

  -- Cursor used to verify parent key value exists.
  -- If present, lock parent key's row so it cannot be deleted
  -- by another transaction until this transaction is
  -- committed or rolled back.
  CURSOR Dummy_cursor (Dn NUMBER) IS
    SELECT Deptno FROM dept
      WHERE Deptno = Dn
        FOR UPDATE OF Deptno;
BEGIN
  OPEN Dummy_cursor (:NEW.Deptno);
  FETCH Dummy_cursor INTO Dummy;

  -- Verify parent key.
  -- If not found, raise user-specified error number & message.
  -- If found, close cursor before allowing triggering statement to complete:
  IF Dummy_cursor%NOTFOUND THEN
    RAISE Invalid_department;
  ELSE
    RAISE valid_department;
  END IF;
  CLOSE Dummy_cursor;
EXCEPTION
  WHEN Invalid_department THEN
    CLOSE Dummy_cursor;
    Raise_application_error(-20000, 'Invalid Department'
      || ' Number' || TO_CHAR(:NEW.deptno));
  WHEN Valid_department THEN
    CLOSE Dummy_cursor;
  WHEN Mutating_table THEN
    NULL;
END;

UPDATE and DELETE RESTRICT Trigger for Parent Table

The following trigger is defined on the dept table to enforce the UPDATE and DELETE RESTRICT referential action on the primary key of the dept table:

CREATE OR REPLACE TRIGGER Dept_restrict
  BEFORE DELETE OR UPDATE OF Deptno ON dept
    FOR EACH ROW

-- Before row is deleted from dept or primary key (DEPTNO) of dept is updated,
-- check for dependent foreign key values in emp;
-- if any are found, roll back.

DECLARE
  Dummy                 INTEGER;  -- Use for cursor fetch
  Employees_present     EXCEPTION;
  employees_not_present EXCEPTION;

  -- Cursor used to check for dependent foreign key values.
  CURSOR Dummy_cursor (Dn NUMBER) IS
    SELECT Deptno FROM emp WHERE Deptno = Dn;

BEGIN
  OPEN Dummy_cursor (:OLD.Deptno);
  FETCH Dummy_cursor INTO Dummy;

  -- If dependent foreign key is found, raise user-specified
  -- error number and message. If not found, close cursor
  -- before allowing triggering statement to complete.

  IF Dummy_cursor%FOUND THEN
    RAISE Employees_present;     -- Dependent rows exist
  ELSE
    RAISE Employees_not_present; -- No dependent rows exist
  END IF;
  CLOSE Dummy_cursor;

EXCEPTION
  WHEN Employees_present THEN
    CLOSE Dummy_cursor;
    Raise_application_error(-20001, 'Employees Present in'
      || ' Department ' || TO_CHAR(:OLD.DEPTNO));
  WHEN Employees_not_present THEN
    CLOSE Dummy_cursor;
END;

Caution:

This trigger does not work with self-referential tables (tables with both the primary/unique key and the foreign key). Also, this trigger does not allow triggers to cycle (such as, A fires B fires A).

UPDATE and DELETE SET NULL Triggers for Parent Table

The following trigger is defined on the dept table to enforce the UPDATE and DELETE SET NULL referential action on the primary key of the dept table:

CREATE OR REPLACE TRIGGER Dept_set_null
  AFTER DELETE OR UPDATE OF Deptno ON dept
    FOR EACH ROW

-- Before row is deleted from dept or primary key (DEPTNO) of dept is updated,
-- set all corresponding dependent foreign key values in emp to NULL:

BEGIN
  IF UPDATING AND :OLD.Deptno != :NEW.Deptno OR DELETING THEN
    UPDATE emp SET emp.Deptno = NULL
      WHERE emp.Deptno = :OLD.Deptno;
   END IF;
END;

DELETE Cascade Trigger for Parent Table

The following trigger on the dept table enforces the DELETE CASCADE referential action on the primary key of the dept table:

CREATE OR REPLACE TRIGGER Dept_del_cascade
  AFTER DELETE ON dept
    FOR EACH ROW

-- Before row is deleted from dept,
-- delete all rows from emp table whose DEPTNO is same as
-- DEPTNO being deleted from dept table:

BEGIN
  DELETE FROM emp
    WHERE emp.Deptno = :OLD.Deptno;
END;

Note:

Typically, the code for DELETE CASCADE is combined with the code for UPDATE SET NULL or UPDATE SET DEFAULT to account for both updates and deletes.

UPDATE Cascade Trigger for Parent Table

The following trigger ensures that if a department number is updated in the dept table, then this change is propagated to dependent foreign keys in the emp table:

-- Generate sequence number to be used as flag
-- for determining if update occurred on column:
CREATE SEQUENCE Update_sequence
  INCREMENT BY 1 MAXVALUE 5000 CYCLE;

CREATE OR REPLACE PACKAGE Integritypackage AS
  Updateseq NUMBER;
END Integritypackage;

CREATE OR REPLACE PACKAGE BODY Integritypackage AS
END Integritypackage;

-- Create flag col:
ALTER TABLE emp ADD Update_id NUMBER;

CREATE OR REPLACE TRIGGER Dept_cascade1 BEFORE UPDATE OF Deptno ON dept
DECLARE
-- Before updating dept table (this is a statement trigger),
-- generate new sequence number
-- & assign it to public variable UPDATESEQ of
-- user-defined package named INTEGRITYPACKAGE:
BEGIN
  Integritypackage.Updateseq := Update_sequence.NEXTVAL;
END;

CREATE OR REPLACE TRIGGER Dept_cascade2
  AFTER DELETE OR UPDATE OF Deptno ON dept
    FOR EACH ROW

-- For each department number in dept that is updated,
-- cascade update to dependent foreign keys in emp table.
-- Cascade update only if child row was not already updated by this trigger:
BEGIN
  IF UPDATING THEN
    UPDATE emp
      SET Deptno = :NEW.Deptno,
        Update_id = Integritypackage.Updateseq   --from 1st
          WHERE emp.Deptno = :OLD.Deptno
            AND Update_id IS NULL;
            /* Only NULL if not updated by 3rd trigger
            fired by same triggering statement */
   END IF;
   IF DELETING THEN
     -- Before row is deleted from dept,
     -- delete all rows from emp table whose DEPTNO is same as
     -- DEPTNO being deleted from dept table:
     DELETE FROM emp
       WHERE emp.Deptno = :OLD.Deptno;
   END IF;
END;

CREATE OR REPLACE TRIGGER Dept_cascade3 AFTER UPDATE OF Deptno ON dept
BEGIN UPDATE emp
  SET Update_id = NULL
    WHERE Update_id = Integritypackage.Updateseq;
END;

Note:

Because this trigger updates the emp table, the Emp_dept_check trigger, if enabled, also fires. The resulting mutating table error is trapped by the Emp_dept_check trigger. Carefully test any triggers that require error trapping to succeed to ensure that they always work properly in your environment.

Trigger for Complex Check Constraints

Triggers can enforce integrity rules other than referential integrity. For example, this trigger performs a complex check before allowing the triggering statement to run.

Note:

You might need to set up the following data structures for the example to work:
CREATE OR REPLACE TABLE Salgrade (
  Grade               NUMBER,
  Losal               NUMBER,
  Hisal               NUMBER,
  Job_classification  NUMBER);
CREATE OR REPLACE TRIGGER Salary_check
  BEFORE INSERT OR UPDATE OF Sal, Job ON Emp99
    FOR EACH ROW
DECLARE
  Minsal               NUMBER;
  Maxsal               NUMBER;
  Salary_out_of_range  EXCEPTION;

BEGIN
/* Retrieve minimum & maximum salary for employee's new job classification
  from SALGRADE table into MINSAL and MAXSAL: */

  SELECT Minsal, Maxsal INTO Minsal, Maxsal
    FROM Salgrade
      WHERE Job_classification = :NEW.Job;

/* If employee's new salary is less than or greater than
   job classification's limits, raise exception.
   Exception message is returned and pending INSERT or UPDATE statement
   that fired the trigger is rolled back:*/

  IF (:NEW.Sal < Minsal OR :NEW.Sal > Maxsal) THEN
    RAISE Salary_out_of_range;
  END IF;
EXCEPTION
  WHEN Salary_out_of_range THEN
    Raise_application_error (-20300,
      'Salary '||TO_CHAR(:NEW.Sal)||' out of range for '
      ||'job classification '||:NEW.Job
      ||' for employee '||:NEW.Ename);
  WHEN NO_DATA_FOUND THEN
    Raise_application_error(-20322,
      'Invalid Job Classification '
      ||:NEW.Job_classification);
END;

Complex Security Authorizations and Triggers

Triggers are commonly used to enforce complex security authorizations for table data. Only use triggers to enforce complex security authorizations that cannot be defined using the database security features provided with the database. For example, a trigger can prohibit updates to salary data of the emp table during weekends, holidays, and nonworking hours.

When using a trigger to enforce a complex security authorization, it is best to use a BEFORE statement trigger. Using a BEFORE statement trigger has these benefits:

  • The security check is done before the triggering statement is allowed to run, so that no wasted work is done by an unauthorized statement.

  • The security check is performed only once for the triggering statement, not for each row affected by the triggering statement.

This example shows a trigger used to enforce security.

Note:

You might need to set up the following data structures for the example to work:
CREATE TABLE Company_holidays (Day DATE);
CREATE OR REPLACE TRIGGER Emp_permit_changes
  BEFORE INSERT OR DELETE OR UPDATE ON Emp99
DECLARE
  Dummy             INTEGER;
  Not_on_weekends   EXCEPTION;
  Not_on_holidays   EXCEPTION;
  Non_working_hours EXCEPTION;
BEGIN
   /* Check for weekends: */
   IF (TO_CHAR(Sysdate, 'DY') = 'SAT' OR
     TO_CHAR(Sysdate, 'DY') = 'SUN') THEN
       RAISE Not_on_weekends;
   END IF;

   /* Check for company holidays: */
   SELECT COUNT(*) INTO Dummy FROM Company_holidays
     WHERE TRUNC(Day) = TRUNC(Sysdate); -- Discard time parts of dates
   IF dummy > 0 THEN
     RAISE Not_on_holidays;
   END IF;

  /* Check for work hours (8am to 6pm): */
  IF (TO_CHAR(Sysdate, 'HH24') < 8 OR
    TO_CHAR(Sysdate, 'HH24') > 18) THEN
      RAISE Non_working_hours;
  END IF;
EXCEPTION
  WHEN Not_on_weekends THEN
    Raise_application_error(-20324,'Might not change '
      ||'employee table during the weekend');
  WHEN Not_on_holidays THEN
    Raise_application_error(-20325,'Might not change '
      ||'employee table during a holiday');
  WHEN Non_working_hours THEN
    Raise_application_error(-20326,'Might not change '
     ||'emp table during nonworking hours');
END;

See Also:

Oracle Database Security Guide for details on database security features

Transparent Event Logging and Triggers

Triggers are very useful when you want to transparently perform a related change in the database following certain events.

The REORDER trigger example shows a trigger that reorders parts as necessary when certain conditions are met. (In other words, a triggering statement is entered, and the PARTS_ON_HAND value is less than the REORDER_POINT value.)

Derived Column Values and Triggers

Triggers can derive column values automatically, based upon a value provided by an INSERT or UPDATE statement. This type of trigger is useful to force values in specific columns that depend on the values of other columns in the same row. BEFORE row triggers are necessary to complete this type of operation for the following reasons:

  • The dependent values must be derived before the INSERT or UPDATE occurs, so that the triggering statement can use the derived values.

  • The trigger must fire for each row affected by the triggering INSERT or UPDATE statement.

The following example illustrates how a trigger can be used to derive new column values for a table whenever a row is inserted or updated.

Note:

You might need to set up the following data structures for the example to work:
ALTER TABLE Emp99 ADD(
   Uppername   VARCHAR2(20),
   Soundexname VARCHAR2(20));
CREATE OR REPLACE TRIGGER Derived 
BEFORE INSERT OR UPDATE OF Ename ON Emp99

/* Before updating the ENAME field, derive the values for
   the UPPERNAME and SOUNDEXNAME fields. Restrict users
   from updating these fields directly: */
FOR EACH ROW
BEGIN
   :NEW.Uppername := UPPER(:NEW.Ename);
   :NEW.Soundexname := SOUNDEX(:NEW.Ename);
END;

Building Complex Updatable Views Using Triggers

Views are an excellent mechanism to provide logical windows over table data. However, when the view query gets complex, the system implicitly cannot translate the DML on the view into those on the underlying tables. INSTEAD OF triggers help solve this problem. These triggers can be defined over views, and they fire instead of the actual DML.

Consider a library system where books are arranged under their respective titles. The library consists of a collection of book type objects. The following example explains the schema.

CREATE OR REPLACE TYPE Book_t AS OBJECT
(
   Booknum   NUMBER,
   Title     VARCHAR2(20),
   Author    VARCHAR2(20),
   Available CHAR(1)
);
CREATE OR REPLACE TYPE Book_list_t AS TABLE OF Book_t;

Assume that the following tables exist in the relational schema:

Table Book_table (Booknum, Section, Title, Author, Available)
Booknum Section Title Author Available
121001 Classic Iliad Homer Y
121002 Novel Gone with the Wind Mitchell M N

Library consists of library_table(section).

Section
Geography
Classic

You can define a complex view over these tables to create a logical view of the library with sections and a collection of books in each section.

CREATE OR REPLACE VIEW Library_view AS
SELECT i.Section, CAST (MULTISET (
   SELECT b.Booknum, b.Title, b.Author, b.Available
   FROM Book_table b
   WHERE b.Section = i.Section) AS Book_list_t) BOOKLIST
FROM Library_table i;

Make this view updatable by defining an INSTEAD OF trigger over the view.

CREATE OR REPLACE TRIGGER Library_trigger INSTEAD OF INSERT ON Library_view FOR EACH ROW
   Bookvar BOOK_T;
   i       INTEGER;
BEGIN
   INSERT INTO Library_table VALUES (:NEW.Section);
   FOR i IN 1..:NEW.Booklist.COUNT LOOP
      Bookvar := Booklist(i);
      INSERT INTO book_table
         VALUES ( Bookvar.booknum, :NEW.Section, Bookvar.Title, Bookvar.Author, bookvar.Available);
   END LOOP;
END;
/

The library_view is an updatable view, and any INSERTs on the view are handled by the trigger that fires automatically. For example:

INSERT INTO Library_view VALUES ('History', book_list_t(book_t(121330, 'Alexander', 'Mirth', 'Y');

Similarly, you can also define triggers on the nested table booklist to handle modification of the nested table element.

Fine-Grained Access Control Using Triggers

You can use LOGON triggers to execute the package associated with an application context. An application context captures session-related information about the user who is logging in to the database. From there, your application can control how much access this user has, based on his or her session information.

Note:

If you have very specific logon requirements, such as preventing users from logging in from outside the firewall or after work hours, consider using Oracle Database Vault instead of LOGON triggers. With Oracle Database Vault, you can create custom rules to strictly control user access.

See Also:

Responding to Database Events Through Triggers

Note:

This topic applies only to simple triggers.

Database event publication lets applications subscribe to database events, just like they subscribe to messages from other applications. The database events publication framework includes the following features:

  • Infrastructure for publish/subscribe, by making the database an active publisher of events.

  • Integration of data cartridges in the server. The database events publication can be used to notify cartridges of state changes in the server.

  • Integration of fine-grained access control in the server.

By creating a trigger, you can specify a subprogram that runs when an event occurs. DML events are supported on tables, and database events are supported on DATABASE and SCHEMA. You can turn notification on and off by enabling and disabling the trigger using the ALTER TRIGGER statement.

This feature is integrated with the Advanced Queueing engine. Publish/subscribe applications use the DBMS_AQ.ENQUEUE procedure, and other applications such as cartridges use callouts.

See Also:

Topics:

How Events Are Published Through Triggers

When the database detects an event, the trigger mechanism executes the action specified in the trigger. The action can include publishing the event to a queue so that subscribers receive notifications. To publish events, use the DBMS_AQ package.

Note:

The database can detect only system-defined events. You cannot define your own events.

When it detects an event, the database fires all triggers that are enabled on that event, except the following:

  • Any trigger that is the target of the triggering event.

    For example, a trigger for all DROP events does not fire when it is dropped itself.

  • Any trigger that was modified, but not committed, within the same transaction as the triggering event.

    For example, recursive DDL within a system trigger might modify a trigger, which prevents the modified trigger from being fired by events within the same transaction.

See Also:

Oracle Database PL/SQL Packages and Types Reference for information about the DBMS_AQ package

Publication Context

When an event is published, certain run-time context and attributes, as specified in the parameter list, are passed to the callout subprogram. A set of functions called event attribute functions are provided.

See Also:

Event Attribute Functions for information about event-specific attributes

For each supported database event, you can identify and predefine event-specific attributes for the event. You can choose the parameter list to be any of these attributes, along with other simple expressions. For callouts, these are passed as IN arguments.

Error Handling

Return status from publication callout functions for all events are ignored. For example, with SHUTDOWN events, the database cannot do anything with the return status.

Execution Model

Traditionally, triggers execute as the definer of the trigger. The trigger action of an event is executed as the definer of the action (as the definer of the package or function in callouts, or as owner of the trigger in queues). Because the owner of the trigger must have EXECUTE privileges on the underlying queues, packages, or subprograms, this action is consistent.

Event Attribute Functions

When the database fires a trigger, you can retrieve certain attributes about the event that fired the trigger. You can retrieve each attribute with a function call. Table 9-3 describes the system-defined event attributes.

Note:

  • The trigger dictionary object maintains metadata about events that will be published and their corresponding attributes.

  • In earlier releases, these functions were accessed through the SYS package. Oracle recommends you use these public synonyms whose names begin with ora_.

  • ora_name_list_t is defined in package DBMS_STANDARD as

    TYPE ora_name_list_t IS TABLE OF VARCHAR2(64);
    

Table 9-3 System-Defined Event Attributes

Attribute Type Description Example
ora_client_ip_address
VARCHAR2

Returns IP address of the client in a LOGON event when the underlying protocol is TCP/IP

DECLARE
  v_addr VARCHAR2(11);
BEGIN
  IF (ora_sysevent = 'LOGON') THEN
    v_addr := ora_client_ip_address;
  END IF;
END;
ora_database_name
VARCHAR2(50)

Database name.

DECLARE
  v_db_name VARCHAR2(50);
BEGIN
  v_db_name := ora_database_name;
END;
ora_des_encrypted_password
VARCHAR2

The DES-encrypted password of the user being created or altered.

IF (ora_dict_obj_type = 'USER') THEN  
  INSERT INTO event_table
    VALUES (ora_des_encrypted_password);
END IF;
ora_dict_obj_name
VARCHAR(30)

Name of the dictionary object on which the DDL operation occurred.

INSERT INTO event_table 
  VALUES ('Changed object is ' || 
           ora_dict_obj_name);
ora_dict_obj_name_list
(name_list OUT ora_name_list_t)
PLS_INTEGER

Return the list of object names of objects being modified in the event.

DECLARE
name_list   DBMS_STANDARD.ora_name_list_t;
number_modified  PLS_INTEGER;
BEGIN
IF (ora_sysevent='ASSOCIATE STATISTICS')
  THEN number_modified :=
       ora_dict_obj_name_list(name_list);
END IF;
END;
ora_dict_obj_owner
VARCHAR(30)

Owner of the dictionary object on which the DDL operation occurred.

INSERT INTO event_table 
  VALUES ('object owner is' || 
           ora_dict_obj_owner);
ora_dict_obj_owner_list
(owner_list OUT ora_name_list_t)
PLS_INTEGER

Returns the list of object owners of objects being modified in the event.

DECLARE
owner_list
   DBMS_STANDARD.ora_name_list_t;
number_modified   PLS_INTEGER;
BEGIN
IF (ora_sysevent='ASSOCIATE STATISTICS')
  THEN number_modified :=
       ora_dict_obj_name_list(owner_list);
END IF;
END;
ora_dict_obj_type
VARCHAR(20)

Type of the dictionary object on which the DDL operation occurred.

INSERT INTO event_table 
  VALUES ('This object is a ' || 
           ora_dict_obj_type);
ora_grantee
(user_list OUT ora_name_list_t)
PLS_INTEGER

Returns the grantees of a grant event in the OUT parameter; returns the number of grantees in the return value.

DECLARE
user_list   DBMS_STANDARD.ora_name_list_t;
number_of_grantees   PLS_INTEGER;
BEGIN
IF (ora_sysevent = 'GRANT') THEN
number_of_grantees := 
 ora_grantee(user_list);
END IF;
END; 
ora_instance_num
NUMBER

Instance number.

IF (ora_instance_num = 1) THEN
  INSERT INTO event_table VALUES ('1');
END IF;
ora_is_alter_column
(column_name IN VARCHAR2)
BOOLEAN

Returns true if the specified column is altered.

IF (ora_sysevent = 'ALTER' AND
    ora_dict_obj_type = 'TABLE') THEN 
 alter_column := ora_is_alter_column('C');
END IF;
ora_is_creating_nested_table
BOOLEAN

Returns true if the current event is creating a nested table

IF (ora_sysevent = 'CREATE' and
    ora_dict_obj_type = 'TABLE' and
    ora_is_creating_nested_table) THEN 
  INSERT INTO event_table
    VALUES ('A nested table is created');
END IF;
ora_is_drop_column
(column_name IN VARCHAR2)
BOOLEAN

Returns true if the specified column is dropped.

IF (ora_sysevent = 'ALTER' AND
    ora_dict_obj_type = 'TABLE') THEN  
 drop_column := ora_is_drop_column('C');
END IF;
ora_is_servererror
BOOLEAN

Returns TRUE if given error is on error stack, FALSE otherwise.

IF ora_is_servererror(error_number) THEN
 INSERT INTO event_table
   VALUES ('Server error!!');
END IF;
ora_login_user
VARCHAR2(30)

Login user name.

SELECT ora_login_user 
FROM DUAL;
ora_partition_pos
PLS_INTEGER

In an INSTEAD OF trigger for CREATE TABLE, the position within the SQL text where you can insert a PARTITION clause.

-- Retrieve ora_sql_txt into
-- sql_text variable first.
v_n := ora_partition_pos;
v_new_stmt := SUBSTR(sql_text,1,v_n - 1)
         || ' ' || my_partition_clause
         || ' ' || SUBSTR(sql_text, v_n));
ora_privilege_list
(privilege_list
 OUT ora_name_list_t)
PLS_INTEGER

Returns the list of privileges being granted by the grantee or the list of privileges revoked from the revokees in the OUT parameter; returns the number of privileges in the return value.

DECLARE
privelege_list
        DBMS_STANDARD.ora_name_list_t;
number_of_privileges   PLS_INTEGER;
BEGIN
IF (ora_sysevent = 'GRANT' OR
    ora_sysevent = 'REVOKE') THEN
  number_of_privileges :=
    ora_privilege_list(privilege_list);
END IF;
END;
ora_revokee
(user_list OUT ora_name_list_t)
PLS_INTEGER

Returns the revokees of a revoke event in the OUT parameter; returns the number of revokees in the return value.

DECLARE
user_list   DBMS_STANDARD.ora_name_list_t;
number_of_users   PLS_INTEGER;
BEGIN
IF (ora_sysevent = 'REVOKE') THEN
number_of_users := ora_revokee(user_list);
END IF;
END;
ora_server_error
NUMBER

Given a position (1 for top of stack), it returns the error number at that position on error stack

INSERT INTO event_table 
  VALUES ('top stack error ' || 
           ora_server_error(1));
ora_server_error_depth
PLS_INTEGER

Returns the total number of error messages on the error stack.

n := ora_server_error_depth;
-- This value is used with other functions -- such as ora_server_error
ora_server_error_msg
(position in pls_integer)
VARCHAR2

Given a position (1 for top of stack), it returns the error message at that position on error stack

INSERT INTO event_table
  VALUES ('top stack error message' ||
           ora_server_error_msg(1));
ora_server_error_num_params
(position in pls_integer)
PLS_INTEGER

Given a position (1 for top of stack), it returns the number of strings that were substituted into the error message using a format like %s.

n := ora_server_error_num_params(1);
ora_server_error_param
(position in pls_integer,
 param in pls_integer)
VARCHAR2

Given a position (1 for top of stack) and a parameter number, returns the matching substitution value (%s, %d, and so on) in the error message.

-- For example, the second %s in a 
-- message: "Expected %s, found %s"
param := ora_server_error_param(1,2);
ora_sql_txt
(sql_text out ora_name_list_t)
PLS_INTEGER

Returns the SQL text of the triggering statement in the OUT parameter. If the statement is long, it is broken into multiple PL/SQL table elements. The function return value shows the number of elements are in the PL/SQL table.

--...
-- Create table event_table
create table event_table (col
 VARCHAR2(2030));
--...
DECLARE
sql_text   DBMS_STANDARD.ora_name_list_t;
n   PLS_INTEGER;
v_stmt VARCHAR2(2000);
BEGIN
n := ora_sql_txt(sql_text);
 
FOR i IN 1..n LOOP
v_stmt := v_stmt || sql_text(i);
END LOOP;
 
INSERT INTO event_table VALUES ('text of
 triggering statement: ' || v_stmt);
END;
ora_sysevent
VARCHAR2(20)

Database event firing the trigger: Event name is same as that in the syntax.

INSERT INTO event_table
  VALUES (ora_sysevent);
ora_with_grant_option
BOOLEAN

Returns true if the privileges are granted with grant option.

IF (ora_sysevent = 'GRANT' and
    ora_with_grant_option = TRUE) THEN
  INSERT INTO event_table 
    VALUES ('with grant option');
END IF;
space_error_info
(error_number OUT NUMBER,
 error_type OUT VARCHAR2,
 object_owner OUT VARCHAR2,
 table_space_name OUT 
  VARCHAR2,
 object_name OUT VARCHAR2,
 sub_object_name OUT VARCHAR2)
BOOLEAN

Returns true if the error is related to an out-of-space condition, and fills in the OUT parameters with information about the object that caused the error.

IF (space_error_info(eno,typ,owner,ts,obj, 
                     subobj) = TRUE) THEN
  DBMS_OUTPUT.PUT_LINE('The object '|| obj 
               || ' owned by ' || owner ||
               ' has run out of space.');
END IF;

Database Events

Database events are related to entire instances or schemas, not individual tables or rows. Triggers associated with startup and shutdown events must be defined on the database instance. Triggers associated with on-error and suspend events can be defined on either the database instance or a particular schema.

Table 9-4 Database Events

Event When Trigger Fires Conditions Restrictions Transaction Attribute Functions
STARTUP

When the database is opened.

None allowed

No database operations allowed in the trigger.

Return status ignored.

Starts a separate transaction and commits it after firing the triggers.

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
SHUTDOWN

Just before the server starts the shutdown of an instance.

This lets the cartridge shutdown completely. For abnormal instance shutdown, this triiger might not fire.

None allowed

No database operations allowed in the trigger.

Return status ignored.

Starts a separate transaction and commits it after firing the triggers.

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
DB_ROLE_CHANGE

When the database is opened for the first time after a role change.

None allowed

Return status ignored.

Starts a separate transaction and commits it after firing the triggers.

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
SERVERERROR

When the error eno occurs. If no condition is given, then this trigger fires whenever an error occurs.

The trigger does not fire on ORA-1034, ORA-1403, ORA-1422, ORA-1423, and ORA-4030 because they are not true errors or are too serious to continue processing. It also fails to fire on ORA-18 and ORA-20 because a process is not available to connect to the database to record the error.

ERRNO = eno

Depends on the error.

Return status ignored.

Starts a separate transaction and commits it after firing the triggers.

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_server_error
ora_is_servererror
space_error_info

Client Events

Client events are the events related to user logon/logoff, DML, and DDL operations.

The LOGON and LOGOFF events allow simple conditions on UID and USER. All other events allow simple conditions on the type and name of the object, as well as functions like UID and USER.

The LOGON event starts a separate transaction and commits it after firing the triggers. All other events fire the triggers in the existing user transaction.

The LOGON and LOGOFF events can operate on any objects. For all other events, the corresponding trigger cannot perform any DDL operations, such as DROP and ALTER, on the object that caused the event to be generated.

The DDL allowed inside these triggers is altering, creating, or dropping a table, creating a trigger, and compile operations.

If an event trigger becomes the target of a DDL operation (such as CREATE TRIGGER), it cannot fire later during the same transaction

Table 9-5 Client Events

Event When Trigger Fires Attribute Functions
BEFORE ALTER

AFTER ALTER

When a catalog object is altered.

ora_sysevent 
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_type 
ora_dict_obj_name
ora_dict_obj_owner
ora_des_encrypted_password
 (for ALTER USER events)
ora_is_alter_column
 (for ALTER TABLE events)
ora_is_drop_column
 (for ALTER TABLE events)
BEFORE DROP

AFTER DROP

When a catalog object is dropped.

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_type
ora_dict_obj_name
ora_dict_obj_owner
BEFORE ANALYZE

AFTER ANALYZE

When an analyze statement is issued

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_name
ora_dict_obj_type
ora_dict_obj_owner
BEFORE ASSOCIATE STATISTICS

AFTER ASSOCIATE STATISTICS

When an associate statistics statement is issued

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_name
ora_dict_obj_type
ora_dict_obj_owner
ora_dict_obj_name_list
ora_dict_obj_owner_list
BEFORE AUDIT
AFTER AUDIT

BEFORE NOAUDIT
AFTER NOAUDIT

When an audit or noaudit statement is issued

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
BEFORE COMMENT

AFTER COMMENT

When an object is commented

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_name
ora_dict_obj_type
ora_dict_obj_owner
BEFORE CREATE 

AFTER CREATE

When a catalog object is created.

ora_sysevent 
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_type 
ora_dict_obj_name
ora_dict_obj_owner
ora_is_creating_nested_table
 (for CREATE TABLE events)
BEFORE DDL

AFTER DDL

When most SQL DDL statements are issued. Not fired for ALTER DATABASE, CREATE CONTROLFILE, CREATE DATABASE, and DDL issued through the PL/SQL subprogram interface, such as creating an advanced queue.

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_name
ora_dict_obj_type
ora_dict_obj_owner
BEFORE DISASSOCIATE STATISTICS

AFTER DISASSOCIATE STATISTICS

When a disassociate statistics statement is issued

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_name
ora_dict_obj_type
ora_dict_obj_owner
ora_dict_obj_name_list
ora_dict_obj_owner_list
BEFORE GRANT

AFTER GRANT

When a grant statement is issued

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_name
ora_dict_obj_type
ora_dict_obj_owner
ora_grantee
ora_with_grant_option
ora_privileges
BEFORE LOGOFF

At the start of a user logoff

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
AFTER LOGON

After a successful logon of a user.

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_client_ip_address
BEFORE RENAME

AFTER RENAME

When a rename statement is issued.

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_name
ora_dict_obj_owner
ora_dict_obj_type
BEFORE REVOKE

AFTER REVOKE

When a revoke statement is issued

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_name
ora_dict_obj_type
ora_dict_obj_owner
ora_revokee
ora_privileges
AFTER SUSPEND

After a SQL statement is suspended because of an out-of-space condition. The trigger must correct the condition so the statement can be resumed.

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_server_error
ora_is_servererror
space_error_info
BEFORE TRUNCATE

AFTER TRUNCATE

When an object is truncated

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_name
ora_dict_obj_type
ora_dict_obj_owner