CREATE TRIGGER command

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.

SYNTAX:

CREATE [OR REPLACE] TRIGGER [schema.]trigger
    {BEFORE | AFTER}
    {DELETE | INSERT | UPDATE [OF column [, column] ...]}
[OR {DELETE | INSERT | UPDATE [OF column [, column] ...]}] ...
    ON [schema.]table
    [ [REFERENCING { OLD [AS] old [NEW [AS] new]
                   | NEW [AS] new [OLD [AS] old] } ]
     FOR EACH ROW
     [WHEN (condition)] ]
    pl/sql_block

where:

OR REPLACE
    recreates the trigger if it already exists.  You can use this option
    to change the definition of an existing trigger without first
    dropping it.

schema
    is the schema to contain the trigger.  If you omit schema, ORACLE
    creates the trigger in your own schema.

trigger
    is the name of the trigger to be created.

BEFORE
    indicates that ORACLE fires the trigger before executing the
    triggering statement.

AFTER
    indicates that ORACLE fires the trigger after executing the
    triggering statement.

DELETE
    indicates that ORACLE fires the trigger whenever a DELETE statement
    removes a row from the table.

INSERT
    indicates that ORACLE fires the trigger whenever an INSERT
    statement adds a row to table.

UPDATE...OF
    indicates that ORACLE fires the trigger whenever an UPDATE statement
    changes a value in one of the columns specified in the OF clause.
    If you omit the OF clause, ORACLE fires the trigger whenever an
    UPDATE statement changes a value in any column of the table.

ON
    specifies the schema and name of the table on which the trigger is
    to be created.  If you omit schema, ORACLE assumes the table is in
    our own schema.  You cannot create a trigger on a table in the
    schema SYS.

REFERENCING
    specifies correlation names.  You can use correlation names in the
    PL/SQL block and WHEN clause 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, you can use this clause to specify different
    correlation names to avoid confusion between the table name and the
    correlation name.

FOR EACH ROW
    designates the trigger to be 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 clause.

    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.

WHEN
    specifies the trigger restriction.  The trigger restriction contains
    a SQL condition that must be satisfied for ORACLE to fire the
    trigger.  This condition must contain correlation names and cannot
    contain a query.

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

pl/sql_block
    is the PL/SQL block that ORACLE executes to fire the trigger.

    Note that the PL/SQL block of a trigger cannot contain transaction
    control SQL statements (COMMIT, ROLLBACK, and SAVEPOINT).

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.

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 these system
    privileges:
            CREATE  TRIGGER
                   This system privilege allows you to create a trigger
                   in your own schema on a table in your own schema.
            CREATE ANY TRIGGER
                   This system privilege allows you to create a trigger
                   in any user's schema on a table in any user's schema.

    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 the procedural
    option.

SEE:
    ALTER TRIGGER, DISABLE, DROP TRIGGER, ENABLE