Skip Headers
Oracle® Database PL/SQL Language Reference
11g Release 2 (11.2)

Part Number E25519-05
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

CREATE TRIGGER Statement

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

The database automatically runs a trigger when specified conditions occur.

Topics

Prerequisites

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

Description of create_trigger.gif follows
Description of the illustration create_trigger.gif

See:

simple_dml_trigger ::=

Description of simple_dml_trigger.gif follows
Description of the illustration simple_dml_trigger.gif

See:

compound_dml_trigger ::=

Description of compound_dml_trigger.gif follows
Description of the illustration compound_dml_trigger.gif

See:

non_dml_trigger ::=

Description of non_dml_trigger.gif follows
Description of the illustration non_dml_trigger.gif

trigger_body ::=

Description of trigger_body.gif follows
Description of the illustration trigger_body.gif

See:

trigger_edition_clause ::=

Description of trigger_edition_clause.gif follows
Description of the illustration trigger_edition_clause.gif

trigger_ordering_clause ::=

Description of trigger_ordering_clause.gif follows
Description of the illustration trigger_ordering_clause.gif

dml_event_clause ::=

Description of dml_event_clause.gif follows
Description of the illustration dml_event_clause.gif

referencing_clause ::=

Description of referencing_clause.gif follows
Description of the illustration referencing_clause.gif

compound_trigger_block ::=

Description of compound_trigger_block.gif follows
Description of the illustration compound_trigger_block.gif

See "declare_section ::=".

timing_point_section ::=

Description of timing_point_section.gif follows
Description of the illustration timing_point_section.gif

timing_point ::=

Description of timing_point.gif follows
Description of the illustration timing_point.gif

tps_body ::=

Description of tps_body.gif follows
Description of the illustration tps_body.gif

See:

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.

Note:

If you create a trigger on a base table of a materialized view, then you must ensure that the trigger does not fire during a refresh of the materialized view. During refresh, the DBMS_MVIEW procedure I_AM_A_REFRESH returns TRUE.

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.

See Also:

Oracle Database Advanced Application Developer's Guide for more information crossedition triggers

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 

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 

Note:

When you create a materialized view log for a table, the database 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 data in the master table. You cannot control the order in which multiple row triggers fire. Therefore, do not write triggers intended to affect the content of the materialized view.

See Also:

INSTEAD OF

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

Restrictions on INSTEAD OF 

Note:

  • If the view is inherently updatable and has INSTEAD OF triggers, the triggers take precedence: The database fires the triggers instead of performing DML on the view.

  • If the view belongs to a hierarchy, then the subviews do not inherit the trigger.

  • The WITH CHECK OPTION for views is not enforced when inserts or updates to the view are done using INSTEAD OF triggers. The INSTEAD OF trigger body must enforce the check. For information about WITH CHECK OPTION, see Oracle Database SQL Language Reference.

  • The database fine-grained access control lets you define row-level security policies on views. These policies enforce specified rules in response to DML operations. If an INSTEAD OF trigger is also defined on the view, then the database does not enforce the row-level security policies, because the database fires the INSTEAD OF trigger instead of running the DML on the view.

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.

Note:

Using OCI functions or the DBMS_LOB package to update LOB values or LOB attributes of object columns does not cause the database to fire triggers defined on the table containing the columns or the attributes.

Restrictions on UPDATE 

See Also:

AS subquery clause of CREATE VIEW in Oracle Database SQL Language Reference for a list of constructs that prevent inserts, updates, or deletes on a view

ON { schema.table | schema.view }

Specifies the database object on which the trigger is to be created:

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:

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.

Note:

Some objects are created, altered, and dropped using PL/SQL APIs (for example, scheduler jobs are maintained by subprograms in the DBMS_SCHEDULER package). Such PL/SQL subprograms do not fire DDL triggers.

The following ddl_event values are valid:

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

See Also:

"Triggers for Publishing Events" for more information about responding to database events through triggers

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

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:

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

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

In the following definitions, A, B, C, and D are reverse crossedition 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.

Note:

DISABLE is especially useful if you are creating a crossedition trigger, which affects the online application being redefined if compilation errors occur.

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

See Also:

Oracle Database SQL Language Reference for information about SQL conditions

Restrictions on WHEN (condition

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 

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:

In other chapters:

See Also:

Oracle Database Advanced Application Developer's Guide for more information about crossedition triggers