CREATE TRIGGER

Note: Descriptions of commands and clauses preceded by are only available if the Oracle objects option is installed on your database server.

Purpose

To create and enable a database trigger. A database trigger is a stored PL/SQL block that is associated with a table. Oracle automatically executes a trigger when a specified SQL statement is issued against the table.

Prerequisites

Before a trigger can be created, the user SYS must run the SQL script DBMSSTDX.SQL. The exact name and location of this script may vary depending on your operating system.

To issue this statement, you must have one of the following system privileges:

If the trigger issues SQL statements or calls procedures or functions, then the owner of the schema to contain the trigger must have the privileges necessary to perform these operations. These privileges must be granted directly to the owner, rather than acquired through roles.

To create a trigger, you must be using Oracle with PL/SQL installed.

Syntax

Keywords and Parameters

Usage Notes

Before Release 7.3, triggers were parsed and compiled whenever a trigger was fired. From Release 7.3 onwards, the compiled version of a trigger is stored in the data dictionary and is called when a trigger is fired. This feature provides a significant performance improvement to applications that use many triggers.

If a trigger produces compilation errors, it still will be created, but it will fail on execution. This means it effectively blocks all triggering DML statements until it is disabled, replaced by a version without compilation errors, or dropped.

To embed a CREATE TRIGGER statement inside an Oracle Precompiler program, you must terminate the statement with the keyword END-EXEC followed by the embedded SQL statement terminator for the specific language.

Triggers

A database trigger is a stored procedure that is associated with a table. Oracle automatically fires, or executes, a trigger when a triggering statement is issued.

You can use triggers for the following purposes:

• to provide sophisticated auditing and transparent event logging
• to automatically generate derived column values
• to enforce complex security authorizations and business constraints
• to maintain replicate asynchronous tables

For more information on how to design triggers for the above purposes, see the "Using Database Triggers" chapter of Oracle8 Server Application Developer's Guide.

Conditional Predicates

When you create a trigger for more than one DML operaton, you can use conditional predicates within the trigger body to execute specific blocks of code, depending on the type of statement that fires the trigger. Conditional predicates are evaluated as follows:

For more information about creating and using conditional predicates in trigger bodies, see the Oracle8 Server Application Developer's Guide.

Example I

The following example uses conditional predicates to provide information about which DML statement fires trigger AUDIT_TRIGGER:

CREATE TRIGGER audit_trigger BEFORE INSERT OR DELETE OR UPDATE

  ON classified_table FOR EACH ROW

  BEGIN

    IF INSERTING THEN

       INSERT INTO audit_table

          VALUES (USER || ' is inserting' ||

                    ' new key: ' || :new.key);

    ELSIF DELETING THEN

       INSERT INTO audit_table

          VALUES (USER || ' is deleting' ||

                    ' old key: ' || :old.key);

    ELSIF UPDATING('FORMULA') THEN

       INSERT INTO audit_table

          VALUES (USER || ' is updating' ||

                    ' old formula: ' || :old.formula ||

                    ' new formula: ' || :new.formula);

    ELSIF UPDATING THEN

       INSERT INTO audit_table

          VALUES (USER || ' is updating' ||

                    ' old key: ' || :old.key ||

                    ' new key: ' || :new.key);

    END IF;

  END;

Parts of a Trigger

The syntax of the CREATE TRIGGER statement includes the following parts of the trigger:

Triggering statement The definition of the triggering statement specifies what SQL statements cause Oracle to fire the trigger.

Trigger restriction The trigger restriction specifies an additional condition that must be satisfied for a row trigger to be fired. You can specify this condition with the WHEN clause. This condition must be a SQL condition, rather than a PL/SQL condition.

Trigger action The trigger action specifies the PL/SQL block Oracle executes to fire the trigger.

Oracle evaluates the condition of the trigger restriction whenever a triggering statement is issued. If this condition is satisfied, then Oracle fires the trigger using the trigger action.

Types of Triggers

You can create different types of triggers. The type of a trigger determines the following things:

• when Oracle fires the trigger in relation to executing the triggering statement
• how many times Oracle fires the trigger

The type of a trigger is based on the use of the following options of the CREATE TRIGGER command:

• BEFORE
• AFTER
• FOR EACH ROW

Using all combinations of the options for the above parts, you can create four basic types of triggers. Table 4-9 describes each type of trigger, its properties, and the options used to create it.

Table 4-9: Types of Triggers

For a single table, you can create each type of trigger for each of the following commands:

• DELETE
• INSERT
• UPDATE

You can also create triggers that fire for more than one command.

If you create multiple triggers of the same type that fire for the same command on the same table, the order in which Oracle fires these triggers is indeterminate. If your application requires that one trigger be fired before another of the same type for the same command, combine these triggers into a single trigger whose trigger action performs the trigger actions of the original triggers in the appropriate order.

Enabling and Disabling Triggers

An existing trigger must be in one of the following states:

When you create a trigger, Oracle enables it automatically.

You can subsequently disable and enable a trigger with one of the following commands:

• the ALTER TRIGGER command with the DISABLE and ENABLE options
• the ALTER TABLE command with the DISABLE and ENABLE clauses

For information on how to enable and disable triggers, see the ALTER TRIGGER command on page 4-164, the ALTER TABLE command on page 4-123, the ENABLE clause on page 4-495, and the DISABLE clause on page 4-449.

Snapshot Log Triggers

When you create a snapshot log for a table, Oracle implicitly creates an AFTER ROW trigger on the table. This trigger inserts a row into the snapshot log whenever an INSERT, UPDATE, or DELETE statement modifies the table's data. Since you cannot control the order in which multiple row triggers fire, you shouldn't write triggers intended to affect the content of the snapshot. For more information on snapshot logs, see the CREATE SNAPSHOT LOG command earlier in this chapter.

Example II

This example creates a BEFORE statement trigger named EMP_PERMIT_CHANGES in the schema SCOTT. This trigger ensures that changes to employee records are only made during business hours on working days:

CREATE TRIGGER scott.emp_permit_changes 

    BEFORE 

    DELETE OR INSERT OR UPDATE 

    ON scott.emp 

    DECLARE 

        dummy  INTEGER; 

    BEGIN 

        /* If today is a Saturday or Sunday, 

           then return an error.*/ 

        IF (TO_CHAR(SYSDATE, 'DY') = 'SAT' OR 

         TO_CHAR(SYSDATE, 'DY') = 'SUN') 

         THEN raise_application_error( -20501, 

       'May not change employee table during the weekend'); 

        END IF; 

       /* Compare today's date with the dates of all 

          company holidays. If today is a company holiday, 

           then return an error.*/ 

        SELECT COUNT(*) 

         INTO dummy 

          FROM company_holidays 

          WHERE day = TRUNC(SYSDATE); 

        IF dummy > 0 

         THEN raise_application_error( -20501, 

          'May not change employee table during a holiday'); 

        END IF; 

        /*If the current time is before 8:00AM or after

          6:00PM, then return an error.    

        */ 

        IF (TO_CHAR(SYSDATE, 'HH24') < 8 OR 

          TO_CHAR(SYSDATE, 'HH24') >= 18) 

          THEN raise_application_error( -20502, 

       'May only change employee table during working hours'); 

        END IF; 

      END; 

Oracle fires this trigger whenever a DELETE, INSERT, or UPDATE statement affects the EMP table in the schema SCOTT.

Since EMP_PERMIT_CHANGES is a BEFORE statement trigger, Oracle fires it once before executing the triggering statement.

The trigger performs the following operations:

1. If the current day is a Saturday or Sunday, the trigger raises an application error with a message that the employee table cannot be changed during weekends.
2. The trigger compares the current date with the dates listed in the table of company holidays.
3. If the current date is a company holiday, the trigger raises an application error with a message that the employee table cannot be changed during holidays.
4. If the current time is not between 8:00AM and 6:00PM, the trigger raises an application error with a message that the employee table can only be changed during business hours.

Example III

This example creates a BEFORE row trigger named SALARY_CHECK in the schema SCOTT. Whenever a new employee is added to the employee table or an existing employee's salary or job is changed, this trigger guarantees that the employee's salary falls within the established salary range for the employee's job:

CREATE TRIGGER scott.salary_check 

    BEFORE 

    INSERT OR UPDATE OF sal, job ON scott.emp 

    FOR EACH ROW 

    WHEN (new.job <> 'PRESIDENT') 

    DECLARE 

       minsal NUMBER; 

       maxsal NUMBER; 

    BEGIN 

        /* Get the minimum and maximum salaries for the

          employee's job from the SAL_GUIDE table.  */ 

        SELECT minsal, maxsal 

         INTO minsal, maxsal 

         FROM sal_guide 

         WHERE job = :new.job; 

         /* If the employee's salary is below the minimum or  */ 

         /* above the maximum for the job, then generate an   */ 

         /* error.*/ 

        IF (:new.sal < minsal OR :new.sal > maxsal) 

        THEN raise_application_error( -20601, 

          'Salary ' || :new.sal || ' out of range for job ' 

          || :new.job || ' for employee ' || :new.ename ); 

        END IF; 

    END; 

Oracle fires this trigger whenever one of the following statements is issued:

• an INSERT statement that adds rows to the EMP table
• an UPDATE statement that changes values of the SAL or JOB columns of the EMP table

Since SALARY_CHECK is a BEFORE row trigger, Oracle fires it before changing each row that is updated by the UPDATE statement or before adding each row that is inserted by the INSERT statement.

SALARY_CHECK has a trigger restriction that prevents it from checking the salary of the company president. For each new or modified employee row that meets this condition, the trigger performs the following steps:

1. The trigger queries the salary guide table for the minimum and maximum salaries for the employee's job.
2. The trigger compares the employee's salary with these minimum and maximum values.
3. If the employee's salary does not fall within the acceptable range, the trigger raises an application error with a message that the employee's salary is not within the established range for the employee's job.

INSTEAD OF TRIGGERS

Use INSTEAD OF triggers to DELETE, UPDATE, or INSERT on views which are not inherently modifiable. See "The View Query" on page 4-434 for a list of constructs that prevent inserts, updates, or deletes on a view. In the following example, customer data is stored in two tables. The object view ALL_CUSTOMERS is created as a UNION of the two tables, CUSTOMERS_SJ and CUSTOMERS_PA. An INSTEAD OF trigger is used to insert values:

CREATE TABLE customers_sj 

  ( cust    NUMBER(6),

    address VARCHAR2(50),

    credit   NUMBER(9,2)  );
CREATE TABLE customers_pa 

  ( cust    NUMBER(6),

    address VARCHAR2(50),

    credit   NUMBER(9,2) );
CREATE TYPE customer_t AS OBJECT

  ( cust    NUMBER(6),

    address   VARCHAR2(50),

    credit    NUMBER(9,2),

    location   VARCHAR2(20)  );
CREATE VIEW all_customers (cust) 

AS SELECT customer_t (cust, address, credit, 'SAN_JOSE')

FROM   customers_sj

UNION ALL

SELECT customer_t(cust, address, credit, 'PALO_ALTO')

FROM   customers_pa;
CREATE TRIGGER instrig INSTEAD OF INSERT ON all_customers 

   FOR EACH ROW 

      BEGIN 

        IF (:new.location = 'SAN_JOSE') THEN 

           INSERT INTO customers_sj 

            VALUES (:new.cust, :new.address, :new.credit); 

        ELSE 

           INSERT INTO customers_pa 

            VALUES (:new.cust, :new.address, :new.credit); 

        END IF; 

       END;

User-Defined Types, LOB, and REF Columns

You can reference and use object, VARRAY, nested table, LOB, and REF columns in the trigger action inside the PL/SQL block, but you cannot modify their values within the trigger action. For an UPDATE trigger, object type, VARRAY type, and REF type, columns can be specified in the OF clause to indicate that the trigger should be fired whenever an UPDATE statement changes a value in one of the columns.

Using OCI functions or the DBMS_LOB package to update LOB values or LOB attributes of object columns will not fire triggers defined on the table containing the columns or the attributes. Performing DML operations directly on nested table columns will not fire triggers defined on the table containing the nested table column.

Nested table and BFILE types can be updated without updating the underlying table on which the trigger is defined. You cannot, therefore, specify nested table or LOB type columns in an OF clause.