CREATE TRIGGER

Purpose

Use the CREATE TRIGGER statement to create and enable a database trigger, which is

• A stored PL/SQL block associated with a table, a schema, or the database

• An anonymous PL/SQL block or a call to a procedure implemented in PL/SQL or Java

Oracle automatically executes a trigger when specified conditions occur.

When you create a trigger, Oracle enables it automatically. You can subsequently disable and enable a trigger with the DISABLE and ENABLE clause of the ALTER TRIGGER or ALTER TABLE statement.

See Also: Oracle8i Concepts for a description of the various types of triggers Oracle8i Application Developer's Guide - Fundamentals for more information on how to design triggers for the above purposes ALTER TRIGGER and ALTER TABLE for information on enabling, disabling, and compiling triggers DROP TRIGGER for information on dropping a trigger

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 depend on your operating system.

• To create a trigger in your own schema on a table in your own schema or on your own schema (SCHEMA), you must have the CREATE TRIGGER privilege.

• To create a trigger in any schema on a table in any schema, or on another user's schema (schema.SCHEMA), you must have the CREATE ANY TRIGGER privilege.

• In addition to the preceding privileges, to create a trigger on DATABASE, you must have the ADMINISTER DATABASE TRIGGER system privilege.

If the trigger issues SQL statements or calls procedures or functions, then the owner of 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.

Syntax

dml_event_clause::=

referencing_clause::=

Keywords and Parameters

OR REPLACE

Specify OR REPLACE to re-create the trigger if it already exists. Use this clause to change the definition of an existing trigger without first dropping it.

schema

Specify the schema to contain the trigger. If you omit schema, Oracle creates the trigger in your own schema.

trigger

Specify the name of the trigger to be created.

If a trigger produces compilation errors, it is still created, but it fails on execution. You can see the associated compiler error messages with the SQL*Plus command SHOW ERRORS. This means it effectively blocks all triggering DML statements until it is disabled, replaced by a version without compilation errors, or dropped.

Note: If you create a trigger on a base table of a materialized view, you must ensure that the trigger does not fire during a refresh of the materialized view. (During refresh, the DBMS_SNAPSHOT procedure I_AM_A_REFRESH returns TRUE.)

BEFORE

Specify BEFORE to cause Oracle to fire the trigger before executing the triggering event. For row triggers, this is a separate firing before each affected row is changed.

Restrictions:

• You cannot specify a BEFORE trigger on a view or an object view.

• When defining a BEFORE trigger for LOB columns, you can read the :OLD value but not the :NEW value. You cannot write either the :OLD or the :NEW value.

AFTER

Specify AFTER to cause Oracle to fire the trigger after executing the triggering event. For row triggers, this is a separate firing after each affected row is changed.

Restrictions:

• You cannot specify an AFTER trigger on a view or an object view.

• When defining an AFTER trigger for LOB columns, you can read the :OLD value but not the :NEW value. You cannot write either the :OLD or the :NEW value.

  Note: When you create a materialized view log for a table, Oracle implicitly creates an AFTER ROW trigger on the table. This trigger inserts a row into the materialized view log whenever an INSERT, UPDATE, or DELETE statement modifies the table's data. You cannot control the order in which multiple row triggers fire. Therefore, you should not write triggers intended to affect the content of the materialized view.

  See Also: CREATE MATERIALIZED VIEW LOG for more information on materialized view logs

INSTEAD OF

Specify INSTEAD OF to cause Oracle to fire the trigger instead of executing the triggering event. By default, INSTEAD OF triggers are activated for each row.

If a view is inherently updatable and has INSTEAD OF triggers, the triggers take preference. In other words, Oracle fires the triggers instead of performing DML on the view.

Restrictions:

• INSTEAD OF is a valid clause only for views. You cannot specify an INSTEAD OF trigger on a table.

• If a view has INSTEAD OF triggers, any views created on it must have INSTEAD OF triggers, even if the views are inherently updatable.

• When defining INSTEAD OF triggers for LOB columns, you can read both the :OLD and the :NEW value, but you cannot write either the :OLD or the :NEW values.

  Note: You can create multiple triggers of the same type (BEFORE, AFTER, or INSTEAD OF) that fire for the same statement 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 statement, combine these triggers into a single trigger whose trigger action performs the trigger actions of the original triggers in the appropriate order.

dml_event_clause

The dml_event_clause lets you specify one of three DML statements that can cause the trigger to fire. Oracle fires the trigger in the existing user transaction.

DELETE	Specify DELETE if you want Oracle to fire the trigger whenever a DELETE statement removes a row from the table or an element from a nested table.
INSERT	Specify INSERT if you want Oracle to fire the trigger whenever an INSERT statement adds a row to table or an element to a nested table.
UPDATE	Specify UPDATE if you want Oracle to fire the trigger whenever an UPDATE statement changes a value in one of the columns specified after OF. If you omit OF, Oracle fires the trigger whenever an UPDATE statement changes a value in any column of the table or nested table. For an UPDATE trigger, you can specify object type, varray, and REF columns after OF to indicate that the trigger should be fired whenever an UPDATE statement changes a value in one of the columns. However, you cannot change the values of these columns in the body of the trigger itself.
	Note: Using OCI functions or the DBMS_LOB package to update LOB values or LOB attributes of object columns does not cause Oracle to fire triggers defined on the table containing the columns or the attributes.
	Restrictions: You cannot specify OF with UPDATE for an INSTEAD OF trigger. Oracle fires INSTEAD OF triggers whenever an UPDATE changes a value in any column of the view. You cannot specify nested table or LOB columns with OF. See Also: AS subquery of CREATE VIEW for a list of constructs that prevent inserts, updates, or deletes on a view
Performing DML operations directly on nested table columns does not cause Oracle to fire triggers defined on the table containing the nested table column

ddl_event

Specify one or more types of DDL statements that can cause the trigger to fire. You can create triggers for these events on DATABASE or SCHEMA unless otherwise noted. You can create BEFORE and AFTER triggers for these events. Oracle fires the trigger in the existing user transaction. The following values are valid:

ALTER	Specify ALTER to fire the trigger whenever an ALTER statement modifies a database object in the data dictionary. Restriction: The trigger will not be fired by an ALTER DATABASE statement.
ANALYZE	Specify ANALYZE to fire the trigger whenever Oracle collects or deletes statistics or validates the structure of a database object.
ASSOCIATE STATISTICS	Specify ASSOCIATE STATISTICS to fire the trigger whenever Oracle associates a statistics type with a database object.
AUDIT	Specify AUDIT to fire the trigger whenever Oracle tracks the occurrence of a SQL statement or tracks operations on a schema object.
COMMENT	Specify COMMENT to fire the trigger whenever a comment on a database object is added to the data dictionary.
CREATE	Specify CREATE to fire the trigger whenever a CREATE statement adds a new database object to the data dictionary. Restriction: The trigger will not be fired by a CREATE DATABASE or CREATE CONTROLFILE statement.
DISASSOCIATE STATISTICS	Specify DISASSOCIATE STATISTICS to fire the trigger whenever Oracle disassociates a statistics type from a database object.
DROP	Specify DROP to fire the trigger whenever a DROP statement removes a database object from the data dictionary.
GRANT	Specify GRANT to fire the trigger whenever a user grants system privileges or roles or object privileges to another user or to a role.
NOAUDIT	Specify NOAUDIT to fire the trigger whenever a NOAUDIT statement instructs Oracle to stop tracking a SQL statement or operations on a schema object.
RENAME	Specify RENAME to fire the trigger whenever a RENAME statement change the name of a database object.
REVOKE	Specify REVOKE to fire the trigger whenever a REVOKE statement removes system privileges or roles or object privileges from a user or role.
TRUNCATE	Specify TRUNCATE to fire the trigger whenever a TRUNCATE statement removes the rows from a table or cluster and resets its storage characteristics.
DDL	Specify DDL to fire the trigger whenever any of the preceding DDL statements is issued.
Restriction: You cannot specify as a triggering event any DDL operation performed through a PL/SQL procedure.

database_event

Specify one or more particular states of the database that can cause the trigger to fire. You can create triggers for these events on DATABASE or SCHEMA unless otherwise noted. For each of these triggering events, Oracle opens an autonomous transaction scope, fires the trigger, and commits any separate transaction (regardless of any existing user transaction).

See Also: PL/SQL User's Guide and Reference for more information on autonomous transaction scope

SERVERERROR	Specify SERVERERROR to fire the trigger whenever a server error message is logged.
	The following errors do not cause a SERVERERROR trigger to fire: ORA-01403: data not found ORA-01422: exact fetch returns more than requested number of rows ORA-01423: error encountered while checking for extra rows in exact fetch ORA-01034: ORACLE not available ORA-04030: out of process memory
LOGON	Specify LOGON to fire the trigger whenever a client application logs onto the database.
LOGOFF	Specify LOGOFF to fire the trigger whenever a client applications logs off the database.
STARTUP	Specify STARTUP to fire the trigger whenever the database is opened.
SHUTDOWN	Specify SHUTDOWN to fire the trigger whenever an instance of the database is shut down.
Notes: Only AFTER triggers are relevant for LOGON, STARTUP, and SERVERERROR. Only BEFORE triggers are relevant for LOGOFF and SHUTDOWN. AFTER STARTUP and BEFORE SHUTDOWN triggers apply only to DATABASE.

ON table | view

The ON clause lets you determine the database object on which the trigger is to be created.

[schema.] table | view	Specify the schema and table or view name of one of the following on which the trigger is to be created: Table or view Object table or object view A column of nested-table type
	If you omit schema, Oracle assumes the table is in your own schema. You can create triggers on index-organized tables. Restriction: You cannot create a trigger on a table in the schema SYS.
NESTED TABLE	Specify the nested_table_column of a view upon which the trigger is being defined. Such a trigger will fire only if the DML operates on the elements of the nested table. Restriction: You can specify NESTED TABLE only for INSTEAD OF triggers.
DATABASE	Specify DATABASE to define the trigger on the entire database.
SCHEMA	Specify SCHEMA to define the trigger on the current schema.

referencing_clause

The referencing_clause lets you specify correlation names. You can use correlation names in the PL/SQL block and WHEN condition of a row trigger to refer specifically to old and new values of the current row. The default correlation names are OLD and NEW. If your row trigger is associated with a table named OLD or NEW, use this clause to specify different correlation names to avoid confusion between the table name and the correlation name.

• If the trigger is defined on a nested table, OLD and NEW refer to the row of the nested table, and PARENT refers to the current row of the parent table.

• If the trigger is defined on an object table or view, OLD and NEW refer to object instances.

Restriction: This clause is valid only for DML event triggers (not DDL or database event triggers).

FOR EACH ROW	Specify FOR EACH ROW to designate the trigger as a row trigger. Oracle fires a row trigger once for each row that is affected by the triggering statement and meets the optional trigger constraint defined in the WHEN condition. Note: This clause is applies only to DML events, not to DDL or database events.
	Except for INSTEAD OF triggers, if you omit this clause, the trigger is a statement trigger. Oracle fires a statement trigger only once when the triggering statement is issued if the optional trigger constraint is met. INSTEAD OF trigger statements are implicitly activated for each row.

WHEN

Specify the trigger restriction, which is a SQL condition that must be satisfied for Oracle to fire the trigger. See the syntax description of condition in "Conditions". This condition must contain correlation names and cannot contain a query.

Restrictions:

• You can specify a trigger restriction only for a row trigger. Oracle evaluates this condition for each row affected by the triggering statement.

• You cannot specify trigger restrictions for INSTEAD OF trigger statements.

• You can reference object columns or their attributes, or varray, nested table, or LOB columns. You cannot invoke PL/SQL functions or methods in the trigger restriction.

pl/sql_block

Specify the PL/SQL block that Oracle executes to fire the trigger.

The PL/SQL block of a database trigger can contain one of a series of built-in functions in the SYS schema designed solely to extract system event attributes. These functions can be used only in the PL/SQL block of a database trigger.

Restrictions:

• The PL/SQL block of a trigger cannot contain transaction control SQL statements (COMMIT, ROLLBACK, SAVEPOINT, and SET CONSTRAINT) if the block is executed within the same transaction.

• You can reference and use LOB columns in the trigger action inside the PL/SQL block, but you cannot modify their values within the trigger action.

  See Also: PL/SQL User's Guide and Reference for information on PL/SQL, including how to write PL/SQL blocks Oracle8i Application Developer's Guide - Fundamentals for information on these functions Oracle8i Application Developer's Guide - Fundamentals

call_procedure_statement

The call_procedure_statement lets you call a stored procedure, rather than specifying inline the trigger code as a PL/SQL block. The syntax of this statement is the same as that for CALL, with the following exceptions:

• You cannot specify the INTO clause of CALL, because it applies only to functions.

• You cannot specify bind variables in expr.

• To reference columns of tables on which the trigger is being defined, you must specify :NEW and :OLD.

  See Also: "Calling a Procedure in a Trigger Body Example"

Examples

DML Trigger Example

This example creates a BEFORE statement trigger named emp_permit_changes in the schema scott. You would write such a trigger to place restrictions on DML statements issued on this table (such as when such statements could be issued).

CREATE TRIGGER scott.emp_permit_changes 
    BEFORE 
    DELETE OR INSERT OR UPDATE 
    ON scott.emp 
       pl/sql block 

Oracle fires this trigger whenever a DELETE, INSERT, or UPDATE statement affects the emp table in the schema scott. The trigger emp_permit_changes is a BEFORE statement trigger, so Oracle fires it once before executing the triggering statement.

DML Trigger Example with Restriction

This example creates a BEFORE row trigger named salary_check in the schema scott. The PL/SQL block might specify, for example, that the employee's salary must fall 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') 
       pl/sql_block

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

salary_check is a BEFORE row trigger, so 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.

Calling a Procedure in a Trigger Body Example

You could create the salary_check trigger described in the preceding example by calling a procedure instead of providing the trigger body in a PL/SQL block. Assume you have defined a procedure scott.salary_check, which verifies that an employee's salary in in an appropriate range. Then you could create the trigger salary_check as follows:

CREATE TRIGGER scott.salary_check
   BEFORE INSERT OR UPDATE OF sal, job ON scott.emp
   FOR EACH ROW
   WHEN (new.job <> 'PRESIDENT')
   CALL check_sal(:new.job, :new.sal, :new.ename);

The procedure check_sal could be implemented in PL/SQL, C, or Java. Also, you can specify :OLD values in the CALL clause instead of :NEW values.

Database Event Trigger Example

This example creates a trigger to log all errors. The PL/SQL block does some special processing for a particular error (invalid logon, error number 1017. This trigger is an AFTER statement trigger, so it is fired after an unsuccessful statement execution (such as unsuccessful logon).

CREATE TRIGGER log_errors AFTER SERVERERROR ON DATABASE 
   BEGIN
      IF (IS_SERVERERROR (1017)) THEN
         <special processing of logon error>
      ELSE
         <log error number>
      END IF;
   END;

DDL Trigger Example

This example creates an AFTER statement trigger on any DDL statement CREATE. Such a trigger can be used to audit the creation of new data dictionary objects in your schema.

CREATE TRIOGGER audit_db_object AFTER CREATE
   ON SCHEMA
      pl/sql_block 

INSTEAD OF Trigger Example

In this 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.cust.location = 'SAN_JOSE') THEN 
        INSERT INTO customers_sj 
        VALUES (:new.cust.cust, :new.cust.address,:new.cust.credit); 
      ELSE 
       INSERT INTO customers_pa 
       VALUES (:new.cust.cust, :new.cust.address, :new.cust.credit); 
      END IF; 
    END;