CREATE TRIGGER Statement

The CREATE TRIGGER statement creates or replaces a database trigger, which is either of these:

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

• An anonymous PL/SQL block or an invocation of a procedure implemented in PL/SQL or Java

The database automatically runs a trigger when specified conditions occur.

Topics

• Prerequisites

• Syntax

• Semantics

• Related Topics

Prerequisites

• To create a trigger in your schema on a table in your schema or on your schema (SCHEMA), you must have the CREATE TRIGGER system 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 system privilege.

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

• In addition to the preceding privileges, to create a crossedition trigger, you must be enabled for editions. For information about enabling editions for a user, see Oracle Database Advanced Application Developer's Guide.

If the trigger issues SQL statements or invokes 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

create_trigger ::=

See:

• "compound_dml_trigger ::="

• "compound_trigger_block ::="

• "non_dml_trigger ::="

• "trigger_body ::="

simple_dml_trigger ::=

See:

• "dml_event_clause ::="

• "referencing_clause ::="

• "trigger_edition_clause ::="

• "trigger_ordering_clause ::="

compound_dml_trigger ::=

See:

• "dml_event_clause ::="

• "referencing_clause ::="

• "trigger_edition_clause ::="

• "trigger_ordering_clause ::="

non_dml_trigger ::=

trigger_body ::=

See:

• "plsql_block ::="

• routine_clause in Oracle Database SQL Language Reference

trigger_edition_clause ::=

trigger_ordering_clause ::=

dml_event_clause ::=

referencing_clause ::=

compound_trigger_block ::=

See "declare_section ::=".

timing_point_section ::=

timing_point ::=

tps_body ::=

See:

• "exception_handler ::="

• "pragma ::="

• "statement ::="

Semantics

OR REPLACE

Re-creates the trigger if it exists, and recompiles it.

Users who were granted privileges on the trigger before it was redefined can still access the procedure without being regranted the privileges.

schema

Name of the schema containing the trigger. Default: your schema.

trigger_name

Name of the trigger to be created.

Triggers in the same schema cannot have the same names. Triggers can have the same names as other schema objects—for example, a table and a trigger can have the same name—however, to avoid confusion, this is not recommended.

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

CROSSEDITION

Creates the trigger as a crossedition trigger. A crossedition trigger must be defined on a table, not a view. Crossedition triggers are valid only with simple or compound DML triggers, not with database definition language (DDL) or database event triggers. A crossedition trigger is intended to fire when DML changes are made in a database while an online application that uses the database is being patched or upgraded with edition-based redefinition. The body of a crossedition trigger is designed to handle these DML changes so that they can be appropriately applied after the changes to the application code are completed.

The handling of DML changes during edition-based redefinition of an online application can entail multiple steps. Therefore, it is likely, though not required, that a crossedition trigger is also a compound trigger, which requires the FOR clause, rather than the BEFORE, AFTER, or INSTEAD OF keywords.

FORWARD

(Default) Creates the trigger as a forward crossedition trigger, which is the type of trigger described in CROSSEDITION.

REVERSE

Creates the trigger as a reverse crossedition trigger, which is intended to fire when the application, after being patched or upgraded with edition-based redefinition, makes DML changes. This trigger propagates data to columns or tables used by the application before it was patched or upgraded.

simple_dml_trigger

Creates a simple DML trigger (described in "DML Triggers"). A simple_dml_trigger must have a trigger_body, not a compound_trigger_block.

BEFORE

Causes the database to fire the trigger before running the triggering event. For row triggers, the trigger fires before each affected row is changed.

Restrictions on BEFORE 

• You cannot specify a BEFORE trigger on a view unless it is an editioning view.

• In a BEFORE statement trigger, or in BEFORE statement section of a compound trigger, you cannot specify either :NEW or :OLD. A BEFORE row trigger or a BEFORE row section of a compound trigger can read and write into the :OLD or :NEW fields.

AFTER

Causes the database to fire the trigger after running the triggering event. For row triggers, the trigger fires after each affected row is changed.

Restrictions on AFTER 

• You cannot specify a AFTER trigger on a view unless it is an editioning view.

• In an AFTER statement trigger or in AFTER statement section of a compound trigger, you cannot specify either :NEW or :OLD. An AFTER row trigger or AFTER row section of a compound trigger can only read but not write into the :OLD or :NEW fields.

INSTEAD OF

Creates an INSTEAD OF trigger (described in "INSTEAD OF Triggers").

Restrictions on INSTEAD OF 

• You can create an INSTEAD OF trigger only on a noneditioning view (not an editioning view or table), or on a nested table column of a noneditioning view (see "NESTED TABLE nested_table_column").

• An INSTEAD OF trigger cannot have a WHEN clause.

• An INSTEAD OF trigger cannot have a column list.

• An INSTEAD OF trigger can read the OLD and NEW values, but cannot change them.

dml_event_clause

Specifies the triggering statements for a DML trigger. The database fires the trigger in the existing user transaction.

DELETE

Causes the database to fire the trigger whenever a DELETE statement removes a row from the table or removes an element from a nested table.

INSERT

Causes the database to fire the trigger whenever an INSERT statement adds a row to a table or adds an element to a nested table.

UPDATE

Causes the database to fire the trigger whenever an UPDATE statement changes a value in a column specified after OF. If you omit OF, then the database 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 ADT, varray, and REF columns after OF to indicate that the trigger must fire whenever an UPDATE statement changes a value in a column. However, you cannot change the values of these columns in the body of the trigger itself.

Restrictions on UPDATE 

• You cannot specify UPDATE OF for an INSTEAD OF trigger. The database fires INSTEAD OF triggers whenever an UPDATE changes a value in any column of the view.

• You cannot specify a nested table or LOB column in the UPDATE OF clause.

  Performing DML operations directly on nested table columns does not cause the database to fire triggers defined on the table containing the nested table column.

ON { schema.table | schema.view }

Specifies the database object 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, the database assumes the table is in your schema.

Restriction on schema.table You cannot create a trigger on a table in the schema SYS.

NESTED TABLE nested_table_column

Specifies the nested_table_column of a view upon which the trigger is being defined. Such a trigger fires only if the DML operates on the elements of the nested table. For more information, see "INSTEAD OF Triggers on Nested Table Columns of Views".

Restriction on NESTED TABLE You can specify NESTED TABLE only for INSTEAD OF triggers.

referencing_clause

Specifies correlation names, which refer to old, new, and parent values of the current row. Defaults: OLD, NEW, and PARENT.

If your trigger is associated with a table named OLD, NEW, or PARENT, then use this clause to specify different correlation names to avoid confusion between the table names and the correlation names.

If the trigger is defined on a nested table, then OLD and NEW refer to the current row of the nested table, and PARENT refers to the current row of the parent table. If the trigger is defined on a database table or view, then OLD and NEW refer to the current row of the database table or view, and PARENT is undefined.

You can use correlation names in any trigger body and in the WHEN condition of a row-level simple DML trigger or a compound DML trigger.

Restrictions on referencing_clause The referencing_clause is not valid with:

• An INSTEAD OF trigger on a CREATE DDL event

• A DML trigger whose body is CALL routine

FOR EACH ROW

Creates the trigger as a row trigger. The database fires a row trigger for each row that is affected by the triggering statement and meets the optional trigger constraint defined in the WHEN condition.

Except for INSTEAD OF triggers, if you omit this clause, then the trigger is a statement trigger. The database fires a statement trigger only when the triggering statement is issued if the optional trigger constraint is met.

INSTEAD OF trigger statements are implicitly activated for each row.

compound_dml_trigger

Creates a compound DML trigger (described in "Compound DML Triggers"). A compound_dml_trigger must have a compound_trigger_block, not a trigger_body.

non_dml_trigger

Defines a system trigger (described in "System Triggers"). A non_dml_trigger must have a trigger_body, not a compound_trigger_block.

ddl_event

One or more types of DDL SQL 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. The database fires the trigger in the existing user transaction.

The following ddl_event values are valid:

• ALTER

  Causes the database to fire the trigger whenever an ALTER statement modifies a database object in the data dictionary. An ALTER DATABASE statement does not fire the trigger.

• ANALYZE

  Causes the database to fire the trigger whenever the database collects or deletes statistics or validates the structure of a database object.

  See Also:

  Oracle Database SQL Language Reference for information about using the SQL statement ANALYZE to collect statistics

• ASSOCIATE STATISTICS

  Causes the database to fire the trigger whenever the database associates a statistics type with a database object.

• AUDIT

  Causes the database to fire the trigger whenever an AUDIT statement is issued.

• COMMENT

  Causes the database to fire the trigger whenever a comment on a database object is added to the data dictionary.

• CREATE

  Causes the database to fire the trigger whenever a CREATE statement adds a database object to the data dictionary. The CREATE DATABASE or CREATE CONTROLFILE statement does not fire the trigger.

• DISASSOCIATE STATISTICS

  Causes the database to fire the trigger whenever the database disassociates a statistics type from a database object.

• DROP

  Causes the database to fire the trigger whenever a DROP statement removes a database object from the data dictionary.

• GRANT

  Causes the database to fire the trigger whenever a user grants system privileges or roles or object privileges to another user or to a role.

• NOAUDIT

  Causes the database to fire the trigger whenever a NOAUDIT statement is issued.

• RENAME

  Causes the database to fire the trigger whenever a RENAME statement changes the name of a database object.

• REVOKE

  Causes the database to fire the trigger whenever a REVOKE statement removes system privileges or roles or object privileges from a user or role.

• TRUNCATE

  Causes the database to fire the trigger whenever a TRUNCATE statement removes the rows from a table or cluster and resets its storage characteristics.

• DDL

  Causes the database to fire the trigger whenever any of the preceding DDL statements is issued.

database_event

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, the database opens an autonomous transaction scope, fires the trigger, and commits any separate transaction (regardless of any existing user transaction).

Each database event is valid in either a BEFORE trigger or an AFTER trigger, but not both. These database_event values are valid:

• AFTER STARTUP

  Causes the database to fire the trigger whenever the database is opened. This event is valid only with DATABASE, not with SCHEMA.

• BEFORE SHUTDOWN

  Causes the database to fire the trigger whenever an instance of the database is shut down. This event is valid only with DATABASE, not with SCHEMA.

• AFTER DB_ROLE_CHANGE

  In a Data Guard configuration, causes the database to fire the trigger whenever a role change occurs from standby to primary or from primary to standby. This event is valid only with DATABASE, not with SCHEMA.

• AFTER SERVERERROR

  Causes the database to fire the trigger whenever a server error message is logged.

  These errors do not cause a SERVERERROR trigger to fire:

  • ORA-00018: maximum number of sessions exceeded

  • ORA-00020: maximum number of processes (string) exceeded

  • ORA-01034: ORACLE not available

  • ORA-01403: no data 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-04030: out of process memory when trying to allocate string bytes (string, string)

• AFTER LOGON

  Causes the database to fire the trigger whenever a client application logs onto the database.

• BEFORE LOGOFF

  Causes the database to fire the trigger whenever a client application logs off the database.

• AFTER SUSPEND

  Causes the database to fire the trigger whenever a server error causes a transaction to be suspended.

DATABASE

Defines the trigger on the entire database. The trigger fires whenever any database user initiates the triggering event.

SCHEMA

Defines the trigger on the current schema. The trigger fires whenever any user connected as schema initiates the triggering event.

FOLLOWS | PRECEDES

Specifies the relative firing of triggers that have the same timing point. It is especially useful when creating crossedition triggers, which must fire in a specific order to achieve their purpose.

Use FOLLOWS to indicate that the trigger being created must fire after the specified triggers. You can specify FOLLOWS for a conventional trigger or for a forward crossedition trigger.

Use PRECEDES to indicate that the trigger being created must fire before the specified triggers. You can specify PRECEDES only for a reverse crossedition trigger.

The specified triggers must exist, and they must have been successfully compiled. They need not be enabled.

If you are creating a noncrossedition trigger, then the specified triggers must be all of the following:

• Noncrossedition triggers

• Defined on the same table as the trigger being created

• Visible in the same edition as the trigger being created

If you are creating a crossedition trigger, then the specified triggers must be all of the following:

• Crossedition triggers

• Defined on the same table or editioning view as the trigger being created, unless you specify FOLLOWS or PRECEDES.

  If you specify FOLLOWS, then the specified triggers must be forward crossedition triggers, and if you specify PRECEDES, then the specified triggers must be reverse crossedition triggers. However, the specified triggers need not be on the same table or editioning view as the trigger being created.

• Visible in the same edition as the trigger being created

In the following definitions, A, B, C, and D are either noncrossedition triggers or forward crossedition triggers:

• If B specifies A in its FOLLOWS clause, then B directly follows A.

• If C directly follows B, and B directly follows A, then C indirectly follows A.

• If D directly follows C, and C indirectly follows A, then D indirectly follows A.

• If B directly or indirectly follows A, then B explicitly follows A (that is, the firing order of B and A is explicitly specified by one or more FOLLOWS clauses).

In the following definitions, A, B, C, and D are reverse crossedition triggers:

• If A specifies B in its PRECEDES clause, then A directly precedes B.

• If A directly precedes B, and B directly precedes C, then A indirectly precedes C.

• If A directly precedes B, and B indirectly precedes D, then A indirectly precedes D.

• If A directly or indirectly precedes B, then A explicitly precedes B (that is, the firing order of A and B is explicitly specified by one or more PRECEDES clauses).

ENABLE

(Default) Creates the trigger in an enabled state.

DISABLE

Creates the trigger in a disabled state, which lets you ensure that the trigger compiles without errors before you enable it.

WHEN (condition)

Specifies a SQL condition that the database evaluates for each row that the triggering statement affects. If the value of condition is TRUE for an affected row, then trigger_body or tps_body runs for that row; otherwise, trigger_body or tps_body does not run for that row. The triggering statement runs regardless of the value of condition.

In a DML trigger, the condition can contain correlation names (see "referencing_clause ::="). In condition, do not put a colon (:) before the correlation name NEW, OLD, or PARENT (in this context, it is not a placeholder for a bind variable).

Restrictions on WHEN (condition) 

• You cannot specify this clause for a STARTUP, SHUTDOWN, or DB_ROLE_CHANGE trigger.

• If you specify this clause for a SERVERERROR trigger, then condition must be ERRNO = error_code.

• If you specify this clause for a simple DML trigger, then you must also specify FOR EACH ROW and you cannot specify INSTEAD OF.

• The condition cannot include a subquery or a PL/SQL expression (for example, an invocation of a user-defined function).

trigger_body

The PL/SQL block or CALL subprogram that the database runs to fire either a simple_dml_trigger or non_dml_trigger. A CALL subprogram is either a PL/SQL subprogram or a Java subprogram in a PL/SQL wrapper.

If trigger_body is a PL/SQL block and it contains errors, then the CREATE [OR REPLACE] statement fails.

Restrictions on trigger_body 

• It cannot appear in a compound DML trigger.

• Its declare_section cannot declare variables of the data type LONG or LONG RAW.

In trigger_body or tps_body, declare_section cannot declare variables of the data type LONG or LONG RAW.

compound_trigger_block

Can appear only in a compound DML trigger.

If the trigger is created on a noneditioning view, then compound_trigger_block must have only one timing point section, whose timing_point must be INSTEAD OF EACH ROW.

If the trigger is created on a table or editioning view, then timing point sections can be in any order, but no timing point section can be repeated.

Restriction on compound_trigger_block The declare_section of compound_trigger_block cannot include PRAGMA AUTONOMOUS_TRANSACTION.

Related Topics

In this chapter:

• "ALTER TRIGGER Statement"

• "DROP TRIGGER Statement"

In other chapters:

• Chapter 9, "PL/SQL Triggers"