CREATE TRIGGER Statement

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 ::=

plsql_trigger_source ::=

See:

• "compound_dml_trigger ::="

• "instead_of_dml_trigger ::="

• "system_trigger ::="

simple_dml_trigger ::=

See:

• "dml_event_clause ::="

• "referencing_clause ::="

• "trigger_body ::="

• "trigger_edition_clause ::="

• "trigger_ordering_clause ::="

instead_of_dml_trigger ::=

See:

• "referencing_clause ::="

• "trigger_body ::="

• "trigger_edition_clause ::="

• "trigger_ordering_clause ::="

compound_dml_trigger ::=

See:

• "compound_trigger_block ::="

• "dml_event_clause ::="

• "referencing_clause ::="

• "trigger_edition_clause ::="

• "trigger_ordering_clause ::="

system_trigger ::=

dml_event_clause ::=

referencing_clause ::=

trigger_edition_clause ::=

trigger_ordering_clause ::=

trigger_body ::=

See:

• "plsql_block ::="

• routine_clause in Oracle Database SQL Language Reference

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 for the trigger to be created. Default: your schema.

trigger

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.

Restrictions on create_trigger See "Trigger Restrictions".

simple_dml_trigger

Creates a simple DML trigger (described in "DML Triggers").

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, the trigger body cannot read :NEW or :OLD. (In a BEFORE row trigger, the trigger body can read and write the :OLD and :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 an AFTER trigger on a view unless it is an editioning view.

• In an AFTER statement trigger, the trigger body cannot read :NEW or :OLD. (In an AFTER row trigger, the trigger body can read but not write the :OLD and :NEW fields.)

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.

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.

[ ENABLE | DISABLE ]

Creates the trigger in an enabled (default) or disabled state. Creating a trigger in a disabled state 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 runs for that row; otherwise, trigger_body does not run for that row. The triggering statement runs regardless of the value of condition.

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) 

• If you specify this clause, then you must also specify FOR EACH ROW.

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

Restriction on trigger_body The declare_section cannot declare variables of the data type LONG or LONG RAW.

instead_of_dml_trigger

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

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

DELETE

If the trigger is created on a noneditioning view, then DELETE causes the database to fire the trigger whenever a DELETE statement removes a row from the table on which the noneditioning view is defined.

If the trigger is created on a nested table column of a noneditioning view, then DELETE causes the database to fire the trigger whenever a DELETE statement removes an element from the nested table.

INSERT

If the trigger is created on a noneditioning view, then INSERT causes the database to fire the trigger whenever an INSERT statement adds a row to the table on which the noneditioning view is defined.

If the trigger is created on a nested table column of a noneditioning view, then INSERT causes the database to fire the trigger whenever an INSERT statement adds an element to the nested table.

UPDATE

If the trigger is created on a noneditioning view, then UPDATE causes the database to fire the trigger whenever an UPDATE statement changes a value in a column of the table on which the noneditioning view is defined.

If the trigger is created on a nested table column of a noneditioning view, then UPDATE causes the database to fire the trigger whenever an UPDATE statement changes a value in a column of the nested table.

nested_table_column

Name of the nested_table_column on which the trigger is to be created. The trigger fires only if the DML operates on the elements of the nested table. 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. For more information, see "INSTEAD OF Triggers on Nested Table Columns of Views".

schema

Name of the schema containing the noneditioning view. Default: your schema.

noneditioning_view

If you specify nested_table_column, then noneditioning_view is the name of the noneditioning view that includes nested_table_column. Otherwise, noneditioning_view is the name of the noneditioning view on which the trigger is to be created.

FOR EACH ROW

For documentation only, because an INSTEAD OF trigger is always a row trigger.

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.

trigger_body

The PL/SQL block or CALL subprogram that the database runs to fire the 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.

Restriction on trigger_body The declare_section cannot declare variables of the data type LONG or LONG RAW.

compound_dml_trigger

Creates a compound DML trigger (described in "Compound DML Triggers").

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 tps_body runs for that row; otherwise, tps_body does not run for that row. The triggering statement runs regardless of the value of condition.

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) 

• If you specify this clause, then you must also specify at least one of these timing points:

  • BEFORE EACH ROW

  • AFTER EACH ROW

  • INSTEAD OF EACH ROW

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

system_trigger

Defines a system trigger (described in "System Triggers").

BEFORE

Causes the database to fire the trigger before running the triggering event.

AFTER

Causes the database to fire the trigger after running the triggering event.

INSTEAD OF

Creates an INSTEAD OF trigger.

Restriction on INSTEAD OF The triggering event must be a CREATE statement.

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 any of these events, but you can create INSTEAD OF triggers only for CREATE 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 of the following database events. You can create triggers for these events on either DATABASE or SCHEMA unless otherwise noted. Each database event is valid in either a BEFORE trigger or an AFTER trigger, but not both. 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).

• 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 both of these conditions are true:

  • A server error message is logged.

  • Oracle relational database management system (RDBMS) determines that it is safe to fire error triggers.

    Examples of when it is unsafe to fire error triggers include:

    • RDBMS is starting up.

    • A critical error has occurred.

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

[schema.]SCHEMA

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

DATABASE

DATABASE defines the trigger on the root. The trigger fires whenever any user of the specified database or pluggable database initiates the triggering event.

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. If the value of condition is TRUE, then trigger_body runs for that row; otherwise, trigger_body does not run for that row. The triggering statement runs regardless of the value of condition.

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.

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

trigger_body

The PL/SQL block or CALL subprogram that the database runs to fire the 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 

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

• The trigger body cannot specify either :NEW or :OLD.

dml_event_clause

Specifies the triggering statements for simple_dml_trigger or compound_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 table or the table on which view is defined.

INSERT

Causes the database to fire the trigger whenever an INSERT statement adds a row to table or the table on which view is defined.

UPDATE [ OF column [, column ] ]

Causes the database to fire the trigger whenever an UPDATE statement changes a value in a specified column. Default: The database fires the trigger whenever an UPDATE statement changes a value in any column of table or the table on which view is defined.

If you specify a column, then you cannot change its value in the body of the trigger.

schema

Name of the schema that contains the database object on which the trigger is to be created. Default: your schema.

table

Name of the database table or object table on which the trigger is to be created.

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

view

Name of the database view or object view on which the trigger is to be created.

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.

Restriction on referencing_clause The referencing_clause is not valid if trigger_body is CALL routine.

trigger_edition_clause

Creates the trigger as a crossedition trigger.

The handling of DML changes during edition-based redefinition (EBR) of an online application can entail multiple steps. Therefore, it is likely, though not required, that a crossedition trigger is also a compound trigger.

Restriction on trigger_edition_clause You cannot define a crossedition trigger on a view.

FORWARD

(Default) Creates the trigger as a forward crossedition trigger. A forward 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 EBR. 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.

REVERSE

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

trigger_ordering_clause

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

compound_trigger_block

Belongs to compound_dml_trigger.

If the trigger is created on a noneditioning view, then compound_trigger_block must have only the INSTEAD OF EACH ROW section.

If the trigger is created on a table or editioning view, then timing point sections can be in any order, but no section can be repeated. The compound_trigger_block cannot have an INSTEAD OF EACH ROW section.

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

BEFORE STATEMENT

Specifies the BEFORE STATEMENT section of a compound_dml_trigger on a table or editioning view. This section causes the database to fire the trigger before running the triggering event.

Restriction on BEFORE STATEMENT This section cannot specify :NEW or :OLD.

BEFORE EACH ROW

Specifies the BEFORE EACH ROW section of a compound_dml_trigger on a table or editioning view. This section causes the database to fire the trigger before running the triggering event. The trigger fires before each affected row is changed.

This section can read and write the :OLD and :NEW fields.

AFTER STATEMENT

Specifies the AFTER STATEMENT section of compound_dml_trigger on a table or editioning view. This section causes the database to fire the trigger after running the triggering event.

Restriction on AFTER STATEMENT This section cannot specify :NEW or :OLD.

AFTER EACH ROW

Specifies the AFTER EACH ROW section of a compound_dml_trigger on a table or editioning view. This section causes the database to fire the trigger after running the triggering event. The trigger fires after each affected row is changed.

This section can read but not write the :OLD and :NEW fields.

INSTEAD OF EACH ROW

Specifies the INSTEAD OF EACH ROW section (the only timing point section) of a compound_dml_trigger on a noneditioning view. The database runs tps_body instead of running the triggering DML statement. For more information, see "INSTEAD OF DML Triggers".

Restriction on INSTEAD OF EACH ROW 

• This section can appear only in a compound_dml_trigger on a noneditioning view.

• This section can read but not write the :OLD and :NEW values.

tps_body

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

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

Restriction on tps_body The declare_section cannot declare variables of the data type LONG or LONG RAW.

Examples

Simple DML Triggers

• Example 9-1, "Trigger Uses Conditional Predicates to Detect Triggering Statement"

• Example 9-2, "Trigger Logs Changes to EMPLOYEES.SALARY"

• Example 9-3, "Conditional Trigger Prints Salary Change Information"

• Example 9-4, "Trigger Modifies LOB Columns"

• Example 9-5, "Trigger with REFERENCING Clause"

• Example 9-6, "Trigger References OBJECT_VALUE Pseudocolumn"

INSTEAD OF DML Triggers

• Example 9-7, "INSTEAD OF Trigger"

• Example 9-8, "INSTEAD OF Trigger on Nested Table Column of View"

Compound DML Triggers

• Example 9-9, "Compound Trigger Logs Changes to One Table in Another Table"

• Example 9-10, "Compound Trigger Avoids Mutating-Table Error"

Triggers for Ensuring Referential Integrity

• Example 9-11, "Foreign Key Trigger for Child Table"

• Example 9-12, "UPDATE and DELETE RESTRICT Trigger for Parent Table"

• Example 9-13, "UPDATE and DELETE SET NULL Trigger for Parent Table"

• Example 9-14, "DELETE CASCADE Trigger for Parent Table"

• Example 9-15, "UPDATE CASCADE Trigger for Parent Table"

• Example 9-16, "Trigger Checks Complex Constraints"

• Example 9-17, "Trigger Enforces Security Authorizations"

• Example 9-18, "Trigger Derives New Column Values"

System Triggers

• Example 9-19, "BEFORE Statement Trigger on Sample Schema HR"

• Example 9-20, "AFTER Statement Trigger on Database"

• Example 9-21, "Trigger Monitors Logons"

• Example 9-22, "INSTEAD OF CREATE Trigger on Schema"

Miscellaneous Trigger Examples

• Example 9-23, "Trigger Invokes Java Subprogram"

• Example 9-24, "Trigger Cannot Handle Exception if Remote Database is Unavailable"

• Example 9-25, "Workaround for "

• Example 9-26, "Trigger Causes Mutating-Table Error"

• Example 9-27, "Update Cascade"

• Example 9-28, "Viewing Information About Triggers"

Related Topics

In this chapter:

• "ALTER TRIGGER Statement"

• "DROP TRIGGER Statement"

In other chapters:

• Chapter 9, "PL/SQL Triggers"