Oracle8 Application Developer's Guide Release 8.0 A58241-01 |
Library | Product | Contents | Index

# 13
Using Database Triggers

This chapter discusses database triggers-procedures that are stored in the database and implicitly executed ("fired") when a table is modified. Topics include:

- Designing Triggers
- Creating Triggers
- When Triggers Are Compiled
- Debugging a Trigger
- Modifying a Trigger
- Enabling and Disabling Triggers
- Listing Information About Triggers
- Examples of Trigger Applications

Note: If you are using Trusted Oracle, see the Trusted Oracle documentation for more information about defining and using database triggers.

## Designing Triggers

Use the following guidelines when designing triggers:

- Use triggers to guarantee that when a specific operation is performed, related actions are performed.
- Use database triggers only for centralized, global operations that should be fired for the triggering statement, regardless of which user or database application issues the statement.
- Do not define triggers that duplicate the functionality already built into Oracle. For example, do not define triggers to enforce data integrity rules that can be easily enforced using declarative integrity constraints.
- Limit the size of triggers (60 lines or fewer is a good guideline). If the logic for your trigger requires much more than 60 lines of PL/SQL code, it is better to include most of the code in a stored procedure, and call the procedure from the trigger.
- Be careful not to create recursive triggers. For example, creating an AFTER UPDATE statement trigger on the EMP table that itself issues an UPDATE statement on EMP causes the trigger to fire recursively until it has run out of memory.

## Creating Triggers

Triggers are created using the CREATE TRIGGER command. This command can be used with any interactive tool, such as SQL*Plus or Enterprise Manager. When using an interactive tool, a solitary slash ( / ) on the last line is used to activate the CREATE TRIGGER statement.

The following statement creates a trigger for the EMP table:

    CREATE 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;
    /

If you enter a SQL statement such as

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

the trigger will fire once for each row that is updated, and it prints the new and old salaries, and the difference.

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

The following sections use this example to illustrate the way that parts of a trigger are specified. For more realistic examples of CREATE TRIGGER statements, see "Examples of Trigger Applications" on page 13-22.

### Prerequisites for Creating Triggers

Before creating any triggers, while connected as SYS, submit the CATPROC.SQL script. This script automatically runs all of the scripts required for, or used within, the procedural extensions to the Oracle Server.

Note: The location of this file is operating system dependent; see your platform-specific Oracle documentation.

### Naming Triggers

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

### The BEFORE and AFTER Options

The BEFORE or AFTER option in the CREATE TRIGGER statement specifies exactly when to file the trigger body in relation to the triggering statement that is being executed. 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.

Note: AFTER row triggers are slightly more efficient than BEFORE row triggers. With BEFORE 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 AFTER row triggers, the data blocks need only be read once for both the triggering statement and the trigger.

### The INSTEAD OF Option

The INSTEAD OF option in the CREATE TRIGGER statement is an alternative to the BEFORE and AFTER options. INSTEAD OF triggers provide a transparent way of modifying views that cannot be modified directly through UPDATE, INSERT, and DELETE statements. These triggers are called INSTEAD OF triggers because, unlike other types of triggers, Oracle fires the trigger instead of executing the triggering statement. The trigger performs update, insert, or delete operations directly on the underlying tables.

Users 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.

By default, INSTEAD OF triggers are activated for each row (see "FOR EACH ROW Option" on page 13-7).

#### Views That Are Not Modifiable

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

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

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

#### Example of an INSTEAD OF Trigger

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

    CREATE VIEW manager_info AS
        SELECT e.name, e.empno, d.dept_type, d.deptno, p.level,
               p.projno
            FROM   emp e, dept d, project p
            WHERE  e.empno =  d.mgr_no
            AND    d.deptno = p.resp_dept;

    CREATE TRIGGER manager_info_insert
    INSTEAD OF INSERT ON manager_info
    REFERENCING NEW AS n                 -- new manager information

    FOR EACH ROW
    BEGIN
      IF NOT EXISTS SELECT * FROM emp
            WHERE emp.empno = :n.empno
      THEN
            INSERT INTO emp
                VALUES(:n.empno, :n.name);
      ELSE
            UPDATE emp SET emp.name = :n.name
                              WHERE emp.empno = :n.empno;
        END IF;

        IF NOT EXISTS SELECT * FROM dept
                     WHERE dept.deptno = :n.deptno
        THEN
                     INSERT INTO dept
                              VALUES(:n.deptno, :n.dept_type);
         ELSE
                     UPDATE dept SET dept.dept_type = :n.dept_type
                              WHERE dept.deptno = :n.deptno;
        END IF;

        IF NOT EXISTS SELECT * FROM project
                     WHERE project.projno = :n.projno
        THEN
                     INSERT INTO project
                              VALUES(:n.projno, :n.project_level);
      ELSE
            UPDATE project SET project.level = :n.level
                WHERE project.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.

#### Object Views and INSTEAD OF Triggers

INSTEAD OF triggers provide the means to modify object view instances on the client-side through OCI calls. (See Oracle Call Interface Programmer's Guide for more information.) To modify an object materialized by an object view in the client-side object cache and flush it back to the persistent store, the user must specify INSTEAD OF triggers, unless the object view is modifiable. If the object is read only, however, it is not necessary to define triggers to pin it.

Note Also: For more examples of INSTEAD OF triggers, see "Updating the Object Views" on page 8-6.

### Triggering Statement

The triggering statement specifies:

- the type of SQL statement that fires the trigger body.

  The possible options include DELETE, INSERT, and UPDATE. One, two, or all three of these options can be included in the triggering statement specification.

- the table associated with the trigger.

  Note: Exactly one table (but not a view) can be specified in the triggering statement.

For example, the PRINT_SALARY_CHANGES trigger on page 13-2 fires after any DELETE, INSERT, or UPDATE on the EMP table. Any of the following statements would trigger the PRINT_SALARY_CHANGES trigger given in the previous example:

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

#### Column List for UPDATE

If a triggering statement specifies UPDATE, an optional list of columns can be included in the triggering statement. If you include a column list, the trigger is fired on an UPDATE statement only when one of the specified columns is updated. If you omit a column list, the trigger is fired 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 might have included a column list in the triggering statement, as in

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

### FOR EACH ROW Option

The FOR EACH ROW option determines whether the trigger is a row trigger or a statement trigger. If you specify FOR EACH ROW, 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 means that the trigger fires only once for each applicable statement, but not separately for each row affected by the statement.

For example, you define the following trigger:

    CREATE 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;

and then issue the SQL statement:

    UPDATE emp SET sal = sal + 1000.0
        WHERE deptno = 20;

If there are five employees in department 20, the trigger will fire five times when this statement is issued, since five rows are affected.

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

    CREATE TRIGGER log_emp_update
    AFTER UPDATE ON emp
    BEGIN
        INSERT INTO emp_log (log_date, action)
            VALUES (SYSDATE, 'EMP COMMISSIONS CHANGED');
    END;

See Also: For the order of trigger firing, see Oracle8 Concepts manual.

### The 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 (a WHEN clause cannot be included in the definition of a statement trigger). If included, the expression in the WHEN clause is evaluated for each row that the trigger affects. If the expression evaluates to TRUE for a row, the trigger body is fired on behalf of that row. However, if the expression evaluates to FALSE or NOT TRUE (that is, unknown, as with nulls) for a row, the trigger body is not fired for that row. The evaluation of the WHEN clause does not have an effect on the execution of the triggering SQL statement (that is, 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 would not be executed 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 below. The expression in a WHEN clause must be a SQL expression and cannot include a subquery. You cannot use a PL/SQL expression (including user-defined functions) in the WHEN clause.

### The Trigger Body

The trigger body is a PL/SQL block that can include SQL and PL/SQL statements. These statements are executed if the triggering statement is issued and the trigger restriction (if included) evaluates to TRUE. The trigger body for row triggers has some special constructs that can be included in the code of the PL/SQL block: correlation names and the REFERENCEING option, and the conditional predicates INSERTING, DELETING, and UPDATING.

#### 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 will no longer exist after the row is deleted, the new values are null.

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, etc.), you can include statements in the trigger body similar to

    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 is fired). If a BEFORE row trigger changes the value of NEW.COLUMN, 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's body, but a colon is not allowed when using the qualifiers in the WHEN clause or the REFERENCING option.

#### The 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". Since this is rare, this option is infrequently used.

For example, assume you have a table named NEW with columns FIELD1 (number) and FIELD2 (character). The following CREATE TRIGGER example shows a trigger associated with the NEW table that can use correlation names and avoid naming conflicts between the correlation names and the table name:

    CREATE 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 is then used in the trigger body.

#### Conditional Predicates

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 execute specific blocks of code, depending on the type of statement that fires the trigger. Assume this is the triggering statement:

    INSERT OR UPDATE ON emp

Within the code of the trigger body, you can include the following conditions:

    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

    CREATE TRIGGER . . .
    . . . UPDATE OF sal, comm ON emp . . .
    BEGIN
    . . . IF UPDATING ('SAL') THEN . . . END IF;
    END;

The code in the THEN clause executes only if the triggering UPDATE statement updates the SAL column. The following statement would fire the above trigger and cause the UPDATING (sal) conditional predicate to evaluate to TRUE:

    UPDATE emp SET sal = sal + 100;

#### Error Conditions and Exceptions in the Trigger Body

If a predefined or user-defined error condition or exception is raised during the execution of a trigger body, 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 integrity constraints.

See Also: For more information about error processing in PL/SQL program units, see "Error Handling" on page 10-30 and "Declaring Exceptions and Exception Handling Routines" on page 10-32.

### 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 TRIGGER example
    AFTER INSERT ON emp
    FOR EACH ROW
    BEGIN
      INSERT INTO emp@remote         -- <- compilation fails here
      VALUES ('x');                  --    when dblink is inaccessible
    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, Oracle cannot validate the statement accessing the remote database, and the compilation fails. The previous example exception statement cannot execute because the trigger does not complete compilation.

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

    CREATE TRIGGER example
    AFTER INSERT ON emp
    FOR EACH ROW
    BEGIN
       insert_row_proc;
    END;

    CREATE PROCEDURE insert_row_proc
    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 calls the stored procedure, 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 a trigger requires some restrictions that are not required for standard PL/SQL blocks. The following sections discuss these restrictions.

#### Valid SQL Statements in Trigger Bodies

The body of a trigger can contain DML SQL statements. It can also contain SELECT statements, but they must be SELECT... INTO... statements or the SELECT statement in the definition of a cursor).

DDL statements are not allowed in the body of a trigger. Also, no transaction control statements are allowed in a trigger. The commands ROLLBACK, COMMIT, and SAVEPOINT cannot be used.

Note: A procedure called by a trigger cannot execute the above transaction control statements because the procedure executes within the context of the trigger body.

Statements inside a trigger can reference remote schema objects. However, pay special attention when calling remote procedures from within a local trigger; since if a timestamp or signature mismatch is found during execution of the trigger, the remote procedure is not executed and the trigger is invalidated.

#### LONG and LONG RAW Datatypes

`LONG` and `LONG` `RAW` datatypes 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` datatype.
- If data from a `LONG` or `LONG` `RAW` column can be converted to a constrained datatype (such as `CHAR` and `VARCHAR2`), a `LONG` or `LONG` `RAW` column can be referenced in a SQL statement within a trigger. Note that the maximum length for these datatypes is 32000 bytes.
- Variables cannot be declared using the `LONG` or `LONG` `RAW` datatypes.
- :`NEW` and :`OLD` cannot be used with `LONG` or `LONG` `RAW` columns.

#### References to Package Variables

If an `UPDATE` or `DELETE` statement detects a conflict with a concurrent `UPDATE`, Oracle 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 is fired again. The rollback to savepoint does not undo changes to any package variables referenced in the trigger. The package should include a counter variable to detect this situation.

#### Row Evaluation Order

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 will be 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, it is best to initialize those variables in a `BEFORE` statement trigger.

When a statement in a trigger body causes another trigger to be fired, the triggers are said to be cascading. Oracle allows up to 32 triggers to cascade at any one time. However, you can effectively limit the number of trigger cascades using the initialization parameter `OPEN_CURSORS`, because a cursor must be opened for every execution of a trigger.

#### Trigger Evaluation Order

Although any trigger can execute a sequence of operations either in-line or by calling procedures, using multiple triggers of the same type enhances database administration by permitting the modular installation of applications that have triggers on the same tables.

Oracle executes all triggers of the same type before executing triggers of a different type. If you have multiple triggers of the same type on a single table, Oracle chooses an arbitrary order to execute these triggers.

See Also: Oracle8 Concepts

manual for more information on the firing order of triggers.

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.

To ensure that multiple triggered actions occur in a specific order, you must consolidate these actions into a single trigger (for example, by having the trigger call a series of procedures).

You cannot open a database that contains multiple triggers of the same type if you are using any version of Oracle before release 7.1, nor can you open such a database if your `COMPATIBLE` initialization parameter is set to a version earlier than 7.1.0.

#### Mutating and Constraining Tables

A mutating table is a table that is currently being modified by an `UPDATE`, `DELETE`, or `INSERT` statement, or a table that might need to be updated by the effects of a declarative `DELETE` `CASCADE` referential integrity constraint. A constraining table is a table that a triggering statement might need to read either directly, for a SQL statement, or indirectly, for a declarative referential integrity constraint. A table is mutating or constraining only to the session that issued the statement in progress.

Tables are never considered mutating or constraining for statement triggers unless the trigger is fired as the result of a `DELETE` `CASCADE`.

For all row triggers, or for statement triggers that were fired as the result of a `DELETE` `CASCADE`, there are two important restrictions regarding mutating and constraining tables. These restrictions prevent a trigger from seeing an inconsistent set of data.

- The SQL statements of a trigger cannot read from (query) or modify a mutating table of the triggering statement.
- The statements of a trigger cannot change the `PRIMARY`, `FOREIGN`, or `UNIQUE` `KEY` columns of a constraining table of the triggering statement.

  There is an exception to this restriction; `BEFORE` `ROW` and `AFTER` `ROW` triggers fired by a single row `INSERT` to a table do not treat that table as mutating or constraining. Note that `INSERT` statements that may involve more than one row, such as `INSERT` `INTO` emp `SELECT` . . ., are not considered single row inserts, even if they only result in one row being inserted.

Figure 13-1 illustrates the restriction placed on mutating tables.

#### Figure 13-1 Mutating Tables

Notice that the SQL statement is executed for the first row of the table and then an `AFTER` `ROW` trigger is fired. In turn, a statement in the `AFTER` `ROW` trigger body attempts to query the original table. However, because the `EMP` table is mutating, this query is not allowed by Oracle. If attempted, a runtime error occurs, the effects of the trigger body and triggering statement are rolled back, and control is returned to the user or application.

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 SQL statement

    DELETE FROM emp WHERE empno = 7499;

is issued, the following error is returned:

    ORA-04091: table SCOTT.EMP is mutating, trigger/function may not see it

Oracle returns this error when the trigger fires since the table is mutating when the first row is deleted. (Only one row is deleted by the statement, since `EMPNO` is a primary key, but Oracle has no way of knowing that.)

If you delete the line "`FOR` `EACH` `ROW`" from the trigger above, the trigger becomes a statement trigger, the table is not mutating when the trigger fires, and the trigger does output the correct data.

If you need to update a mutating or constraining table, you could use a temporary table, a PL/SQL table, or a package variable to bypass these restrictions. For example, in place of a single `AFTER` row trigger that updates the original table, resulting in a mutating table error, you may be able to 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 integrity constraints are checked at various times with respect to row triggers.

See Also: Oracle8 Concepts for information about the interaction of triggers and integrity constraints.

Because declarative referential integrity constraints are currently not supported between tables on different nodes of a distributed database, the constraining 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 a Net8 path back to the database that contains the link.

You should not use loop-back database links to circumvent the trigger restrictions. Such applications might behave unpredictably.

### Who Is the Trigger User?

If you issue the statement

    SELECT username FROM USER_USERS

in a trigger, the name of the owner of the trigger is returned, not the name of user who is updating the table.

### Privileges Required to Create Triggers

To create a trigger in your schema, you must have the `CREATE` `TRIGGER` system privilege, and either

- own the table specified in the triggering statement, or
- have the `ALTER` privilege for the table in the triggering statement, or
- have the `ALTER` `ANY` `TABLE` system privilege

To create a trigger in another user's schema, you must have the `CREATE` `ANY` `TRIGGER` system privilege. With this privilege, the trigger can be created in any schema and can be associated with any user's table.

### Privileges for Referenced Schema Objects

The object privileges to the schema objects referenced in the trigger body must be granted to the trigger's owner explicitly (not via a role). The statements in the trigger body operate under the privilege domain of the trigger's owner, not the privilege domain of the user issuing the triggering statement. This is similar to stored procedures.

See Also: "Privileges Required to Execute a Procedure" on page 10-40.

## When Triggers Are Compiled

Triggers are similar to PL/SQL anonymous blocks with the addition of the :`NEW` and :`OLD` capabilities, but their compilation is different. A PL/SQL anonymous block is compiled each time it is loaded into memory. Compilation involves 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: the pcode is generated

Triggers, in contrast, are fully compiled when the `CREATE` `TRIGGER` command is issued, and the pcode is stored in the data dictionary. Hence, firing the trigger no longer requires the opening of a shared cursor to run the trigger action. Instead, the trigger is executed directly.

If errors occur during the compilation of a trigger, the trigger is still created. If a DML statement fires this trigger, the DML statement will fail. (Runtime trigger errors always cause the DML statement to fail.) You can use the `SHOW` `ERRORS` command in SQL*Plus or Enterprise Manager to see any compilation errors when you create a trigger, or you can `SELECT` the errors from the `USER_ERRORS` view.

### Dependencies

Compiled triggers have dependencies. They become invalid if a depended-on object, such as a stored procedure or a function called 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 statement

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

shows the dependencies for the triggers in the `SCOTT` schema.

### Recompiling a Trigger

Use the `ALTER` `TRIGGER` command to recompile a trigger manually. For example, the command

    ALTER TRIGGER print_salary_changes COMPILE;

recompiles the `PRINT_SALARY_CHANGES` trigger.

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

### Migration Issues

Non-compiled triggers cannot be fired under compiled trigger releases (such as Oracle 7.3 and Oracle8). If upgrading from a non-compiled trigger release to a compiled trigger release, all existing triggers must be compiled. The upgrade script cat73xx.sql invalidates all triggers so that they are automatically recompiled when first executed. (The xx stands for a variable minor release number.)

Downgrading from Oracle 7.3 or later to a release prior to 7.3 requires that you execute the cat73xxd.sql downgrade script. This handles portability issues between stored and non-stored trigger releases.

## Debugging a Trigger

You can debug a trigger using the same facilities available for stored procedures.

Note Also: "Debugging" on page 10-37

## Modifying a Trigger

Like a stored procedure, a trigger cannot be explicitly altered; it must be replaced with a new definition. (The `ALTER` `TRIGGER` command 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` command, and you can rerun the `CREATE` `TRIGGER` command.

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

## Enabling and Disabling Triggers

A trigger can be in one of two distinct modes:

enabled | An enabled trigger executes its trigger body if a triggering statement is issued 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 issued and the trigger restriction (if any) evaluates to `TRUE`.

### Disabling Triggers

You might temporarily disable a trigger if

- an object it references is not available
- you have to perform a large data load and want it to proceed quickly without firing triggers
- you are reloading data

By default, triggers are enabled when first created. Disable a trigger using the `ALTER` `TRIGGER` command with the `DISABLE` option. For example, to disable the trigger named `REORDER` of the `INVENTORY` table, enter the following statement:

    ALTER TRIGGER reorder DISABLE;

All triggers associated with a table can be disabled with one statement using the `ALTER` `TABLE` command 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;

### Enabling Triggers

By default, a trigger is automatically enabled when it is created; however, it can later be disabled. Once you have completed the task that required the trigger to be disabled, re-enable the trigger so that it fires when appropriate.

Enable a disabled trigger using the `ALTER` `TRIGGER` command with the `ENABLE` option. To enable the disabled trigger named `REORDER` of the `INVENTORY` table, enter the following statement:

    ALTER TRIGGER reorder ENABLE;

All triggers defined for a specific table can be enabled with one statement using the `ALTER` `TABLE` command with the `ENABLE` clause with 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;

### Privileges Required to Enable and Disable Triggers

To enable or disable triggers using the `ALTER` `TABLE` command, you must either own the table, have the `ALTER` schema object privilege for the table, or have the `ALTER` `ANY` `TABLE` system privilege.

To enable or disable triggers using the `ALTER` `TRIGGER` command, you must own the trigger or have the `ALTER` `ANY` `TRIGGER` system privilege.

## Listing Information About Triggers

The following data dictionary views reveal information about triggers:

- `USER_TRIGGERS`
- `ALL_TRIGGERS`
- `DBA_TRIGGERS`

The Oracle8 Reference gives a complete description of these data dictionary views. For example, assume the following statement was used to create the `REORDER` trigger:

    CREATE 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 name = 'REORDER';

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

    SELECT trigger_body
       FROM user_triggers
       WHERE 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 an Oracle database. For example, triggers are commonly used to

- provide sophisticated auditing
- prevent invalid transactions
- enforce referential integrity (either those actions not supported by declarative integrity 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

This section provides an example of each of the above trigger applications. These examples are not meant to be used as is, but 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 Oracle. Although triggers can be written to record information similar to that recorded by the `AUDIT` command, triggers should be used only when more detailed audit information is required. For example, use triggers to provide value-based auditing on a per-row basis tables.

Sometimes, the Oracle `AUDIT` command 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 Oracle's auditing features provide, compared to auditing defined by triggers.

DML as well as DDL auditing | Standard auditing options permit auditing of DML and DDL statements regarding all types of schema objects and structures. Comparatively, triggers only permit auditing of DML statements issued against tables.
Centralized audit trail | All database audit information is recorded centrally and automatically using the auditing features of Oracle.
Declarative method | Auditing features enabled using the standard Oracle 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 issued (`BY` `ACCESS`) or once for every session that issues 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, any audit information generated by a trigger is rolled back if the triggering statement is rolled back.
Sessions can be audited | Connections and disconnections, as well as session activity (physical I/Os, logical I/Os, deadlocks, etc.), can be recorded using standard database auditing.

When using triggers to provide sophisticated auditing, `AFTER` triggers are normally used. By using `AFTER` triggers, auditing information is recorded after the triggering statement is subjected to any applicable integrity constraints, preventing cases where the audit processing is carried out unnecessarily for statements that generate exceptions to integrity constraints.

When to use `AFTER` row vs. `AFTER` statement triggers depends on the information being audited. For example, row triggers provide value-based auditing on a per-row basis for tables. 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 on a per-row basis. It requires that a "reason code" be stored in a global package variable before the update.

##### Example

This trigger demonstrates

- how triggers can be used to provide value-based auditing
- how to use public package variables

Comments within the code explain the functionality of the trigger.

    CREATE TRIGGER audit_employee
    AFTER INSERT OR DELETE OR UPDATE ON emp
    FOR EACH ROW
    BEGIN
    /* AUDITPACKAGE is a package with a public package
       variable REASON.  REASON could be set by the
       application by a command such as EXECUTE
       AUDITPACKAGE.SET_REASON(reason_string). Note that 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 the above conditional evaluates to TRUE, the
       user-specified error number and message is raised,
       the trigger stops execution, and the effects of the
       triggering statement are rolled back.  Otherwise, a
       new row is inserted into the predefined auditing
       table named AUDIT_EMPLOYEE containing the existing
       and new values of the EMP table and the reason code
       defined by the REASON variable of AUDITPACKAGE.  Note
       that the "old" values are NULL if triggering
       statement is an INSERT and the "new" values are NULL
       if the triggering statement is a DELETE. */

    INSERT INTO audit_employee VALUES
       (:old.ssn, :old.name, :old.job_classification, :old.sal,
       :new.ssn, :new.name, :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 executed:

    CREATE 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 both fired by the same type of SQL statement. However, the `AFTER` row trigger is fired once for each row of the table affected by the triggering statement, while the `AFTER` statement trigger is fired only once after the triggering statement execution is completed.

Another example of using triggers to do auditing is shown below. This trigger tracks changes being made to the `EMP` table, and stores this information in `AUDIT_TABLE` and `AUDIT_TABLE_VALUES`.

    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, and the terminal of the 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 the deleted row
          ELSIF DELETING THEN
             INSERT INTO audit_table
                VALUES (audit_seq.NEXTVAL, user, time_now,
                   terminal, 'EMP', 'DELETE', :old.empno);

          -- for updates, record the primary key
          -- of the row being updated
          ELSE
             INSERT INTO audit_table
                VALUES (audit_seq.NEXTVAL, user, time_now,
                   terminal, 'EMP', 'UPDATE', :old.empno);

             -- and for SAL and DEPTNO, record old and 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;
    /

### Integrity Constraints and Triggers

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

Declarative integrity 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.

See Also: Chapter 9, "Maintaining Data Integrity"

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 Oracle's declarative integrity constraint features, triggers should only be used to enforce complex business rules that cannot be defined using standard integrity constraints. The declarative integrity constraint features provided with Oracle 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 integrity constraints corresponding to each schema object.
Declarative method | Constraints defined using the standard integrity 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 integrity constraints, triggers can be used to enforce complex business constraints not definable using declarative integrity constraints. For example, triggers can be used to enforce

- `UPDATE` and `DELETE` `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

#### Enforcing Referential Integrity Using Triggers

Many cases of referential integrity can be enforced using triggers. However, only use triggers when you want to enforce the `UPDATE` and `DELETE` `SET` `NULL` (when referenced data is updated or deleted, all associated dependent data is site to `NULL`), and `UPDATE` and `DELETE` `SET` `DEFAULT` (when referenced data is updated or deleted, all associated dependent data is set to a default value) referential actions, or when you want to enforce referential integrity between parent and child tables on different nodes of a distributed database.

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, you can also declare the foreign key in the child table, but disable it; this 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:

- A trigger must be defined for the child table that guarantees values inserted or updated in the foreign key correspond to values in the parent key.
- One or more triggers must be defined for the parent table. These triggers guarantee the desired referential action (`RESTRICT`, `CASCADE`, or `SET` `NULL`) for values in the foreign key when values are updated or deleted in the parent key. No action is required for inserts into the parent table (no dependent foreign keys exist).

The following sections provide examples of the triggers necessary to enforce referential integrity. The `EMP` and `DEPT` table relationship is used in these examples.

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 example below 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 TRIGGER emp_dept_check
    BEFORE INSERT OR UPDATE OF deptno ON emp
    FOR EACH ROW WHEN (new.deptno IS NOT NULL)

    -- Before a row is inserted, or DEPTNO is updated in the EMP
    -- table, fire this trigger to verify that the new foreign
    -- key value (DEPTNO) is present in the DEPT table.
    DECLARE
       dummy INTEGER;  -- used for cursor fetch below
       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 can't be
    -- deleted by another transaction until this
    -- transaction is committed or rolled back.
    CURSOR PRINT_SALARY_CHANGES_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 and 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 the 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 TRIGGER dept_restrict
    BEFORE DELETE OR UPDATE OF deptno ON dept
    FOR EACH ROW

    -- Before a row is deleted from DEPT or the primary key
    -- (DEPTNO) of DEPT is updated, check for dependent
    -- foreign key values in EMP; rollback if any are found.
    DECLARE
       dummy INTEGER;      -- used for cursor fetch below
       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 */
       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;

WARNING: This trigger will not work with self-referential tables (that is, 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 TRIGGER dept_set_null
    AFTER DELETE OR UPDATE OF deptno ON dept
    FOR EACH ROW

    -- Before a row is deleted from DEPT or the 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 TRIGGER dept_del_cascade
    AFTER DELETE ON dept
    FOR EACH ROW

    -- Before a row is deleted from DEPT, delete all
    -- rows from the EMP table whose DEPTNO is the same as
    -- the DEPTNO being deleted from the 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, this change is propagated to dependent foreign keys in the `EMP` table:

    -- Generate a sequence number to be used as a flag for
    -- determining if an update has occurred on a column.
    CREATE SEQUENCE update_sequence
        INCREMENT BY 1 MAXVALUE 5000
        CYCLE;

    CREATE PACKAGE integritypackage AS
       updateseq NUMBER;
    END integritypackage;

    CREATE or replace PACKAGE BODY integritypackage AS
    END integritypackage;
    ALTER TABLE emp ADD update_id NUMBER;   -- create flag col.

    CREATE TRIGGER dept_cascade1 BEFORE UPDATE OF deptno ON dept
    DECLARE
       dummy NUMBER;

    -- Before updating the DEPT table (this is a statement
    -- trigger), generate a new sequence number and assign
    -- it to the public variable UPDATESEQ of a user-defined
    -- package named INTEGRITYPACKAGE.
    BEGIN
       SELECT update_sequence.NEXTVAL
          INTO dummy
          FROM dual;
       integritypackage.updateseq := dummy;
    END;

    CREATE TRIGGER dept_cascade2 AFTER DELETE OR UPDATE
       OF deptno ON dept FOR EACH ROW

    -- For each department number in DEPT that is updated,
    -- cascade the update to dependent foreign keys in the
    -- EMP table.  Only cascade the update if the child row
    -- has not already been 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 the 3rd trigger
                fired by this same triggering statement */
       END IF;
       IF DELETING THEN

       -- Before a row is deleted from DEPT, delete all
       -- rows from the EMP table whose DEPTNO is the same as
       -- the DEPTNO being deleted from the DEPT table.
          DELETE FROM emp
          WHERE emp.deptno = :old.deptno;
       END IF;
    END;
    CREATE 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, is also fired. The resulting mutating table error is trapped by the EMP_DEPT_CHECK trigger. You should carefully test any triggers that require error trapping to succeed to ensure that they will always work properly in your environment.

#### Enforcing 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 execute. Comments within the code explain the functionality of the trigger.

    CREATE TRIGGER salary_check
    BEFORE INSERT OR UPDATE OF sal, job ON emp
    FOR EACH ROW
    DECLARE
       minsal                NUMBER;
       maxsal                NUMBER;
       salary_out_of_range   EXCEPTION;
    BEGIN

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

    SELECT minsal, maxsal INTO minsal, maxsal FROM salgrade
       WHERE job_classification = :new.job;

    /* If the employee's new salary is less than or greater
       than the job classification's limits, the exception is
       raised.  The exception message is returned and the
       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.name);
       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 Oracle. For, example, a trigger can prohibit updates to salary data of the `EMP` table during weekends, holidays, and non-working 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 execute 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.

##### Example

This example shows a trigger used to enforce security. The Comments within the code explain the functionality of the trigger.

    CREATE TRIGGER emp_permit_changes
    BEFORE INSERT OR DELETE OR UPDATE ON emp
    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);
          /* TRUNC gets rid of 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,'May not change '
             ||'employee table during the weekend');
       WHEN not_on_holidays THEN
          raise_application_error(-20325,'May not change '
             ||'employee table during a holiday');
       WHEN non_working_hours THEN
          raise_application_error(-20326,'May not change '
          ||'emp table during non-working hours');
    END;

### Transparent Event Logging and Triggers

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

##### Example

The `REORDER` trigger example on page 13-21 shows a trigger that reorders parts as necessary when certain conditions are met (that is, a triggering statement is issued 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 because

- 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.

##### Example

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. Comments within the code explain its functionality.

    BEFORE INSERT OR UPDATE OF ename ON emp

    /* Before updating the ENAME field, derive the values for
       the UPPERNAME and SOUNDEXNAME fields. Users should be
       restricted from updating these fields directly. */
    FOR EACH ROW

    BEGIN
       :new.uppername := UPPER(:new.ename);
       :new.soundexname := SOUNDEX(:new.ename);
    END;

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index