Skip Headers
Oracle® Database PL/SQL Language Reference
11g Release 2 (11.2)
Part Number E10472-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
View PDF

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

create_trigger ::=

create_trigger
Description of the illustration create_trigger.gif

See:

simple_dml_trigger ::=

simple_dml_trigger
Description of the illustration simple_dml_trigger.gif

See:

compound_dml_trigger ::=

compound_dml_trigger
Description of the illustration compound_dml_trigger.gif

See:

non_dml_trigger ::=

non_dml_trigger
Description of the illustration non_dml_trigger.gif

trigger_body ::=

trigger_body
Description of the illustration trigger_body.gif

See:

trigger_edition_clause ::=

trigger_edition_clause
Description of the illustration trigger_edition_clause.gif

trigger_ordering_clause ::=

trigger_ordering_clause
Description of the illustration trigger_ordering_clause.gif

dml_event_clause ::=

dml_event_clause
Description of the illustration dml_event_clause.gif

referencing_clause ::=

referencing_clause
Description of the illustration referencing_clause.gif

compound_trigger_block ::=

compound_trigger_block
Description of the illustration compound_trigger_block.gif

See declare_section ::=.

timing_point_section ::=

timing_point_section
Description of the illustration timing_point_section.gif

timing_point ::=

timing_point_section
Description of the illustration timing_point.gif

tps_body ::=

tps_body
Description of the illustration tps_body.gif

See:

Semantics

OR REPLACE

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

schema

The name of the schema containing the trigger. The default is your own schema.

trigger

The name of the trigger to be created.

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 database manipulation language (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

Creates the trigger as a forward crossedition trigger, which is the type of trigger described in CROSSEDITION. This is the default.

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. Such a trigger can be used to propagate 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 the trigger as a single trigger on a DML event.

BEFORE

Causes the database to fire the trigger before running the triggering event. For row triggers, the trigger is fired 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 is fired 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

Causes the database to fire the trigger instead of running the triggering event.

Restrictions on INSTEAD OF 

See Also:

"Creating an INSTEAD OF Trigger: Example"

dml_event_clause

Specifies one of three DML statements that can cause the trigger to fire. The database fires the trigger in the existing user transaction.

You cannot specify the MERGE keyword in the DML_event_clause. If you want a trigger to fire in relation to a MERGE operation, then you must create triggers on the INSERT and UPDATE operations to which the MERGE operation decomposes.

See Also:

"Creating a DML Trigger: Examples"

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

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. The default correlation names are 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 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, then OLD and NEW refer to object instances.

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

Restriction on the referencing_clause The referencing_clause is not valid with INSTEAD OF triggers on CREATE DDL events.

FOR EACH ROW

Creates the trigger as a row trigger. The database 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.

Except for INSTEAD OF triggers, if you omit this clause, then the trigger is a statement trigger. The database 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.

Restriction on FOR EACH ROW This clause is valid only for simple DML triggers, not for compound DML triggers or for DDL or database event triggers.

compound_dml_trigger

Defines a compound trigger on a DML event. The body of a compound trigger must be a compound_trigger_block.

See Also:

"Compound Trigger Restrictions"

non_dml_trigger

Defines a single trigger on a DDL or database event.

ddl_event

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. The database fires the trigger in the existing user transaction.

Restriction on ddl_event You cannot specify as a triggering event any DDL operation performed through a PL/SQL procedure.

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:

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.

See Also:

"Creating a SCHEMA Trigger: Example"

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 | DISABLE

Creates the trigger in an enabled or disabled state. Creating a trigger in a disabled state lets you ensure that the trigger compiles without errors before you put it into use.

Specify DISABLE to create the trigger in disabled form. You can subsequently issue an ALTER TRIGGER ... ENABLE or ALTER TABLE ... ENABLE ALL TRIGGERS statement to enable the trigger. If you omit this clause, then the trigger is enabled when it is created.

Note:

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

See Also:

WHEN (condition)

Specifies a SQL condition that must be satisfied for the database to fire the trigger. This condition can contain correlation names.

When specified in the WHEN clause, the keywords NEW, OLD, and PARENT are not considered bind variables, so are not preceded by a colon (:). However, you must precede NEW, OLD, and PARENT with a colon in all other contexts.

See Also:

Restrictions on WHEN (condition) 

trigger_body

The PL/SQL block, PL/SQL compound trigger block, or call procedure that the database runs to fire the trigger.

compound_trigger_block

Can appear only in a compound_dml_trigger. Timing point sections can be in any order, but no timing point section can be repeated. The declare_section of a compound trigger block cannot include PRAGMA AUTONOMOUS_TRANSACTION.

Examples

Creating a DML Trigger: Examples This example shows the basic syntax for a BEFORE statement trigger. Write such a trigger to place restrictions on DML statements issued on a table, for example, when such statements might be issued.

CREATE TRIGGER schema.trigger_name 
    BEFORE 
    DELETE OR INSERT OR UPDATE 
    ON schema.table_name 
       pl/sql_block

the database fires such a trigger whenever a DML statement affects the table. This trigger is a BEFORE statement trigger, so the database fires it once before running the triggering statement.

The next example shows a partial BEFORE row trigger. The PL/SQL block might specify, for example, that an employee's salary must fall in the established salary range for the employee's job:

CREATE TRIGGER hr.salary_check
      BEFORE INSERT OR UPDATE OF salary, job_id ON hr.employees
      FOR EACH ROW
         WHEN (new.job_id <> 'AD_VP')
      pl/sql_block

the database fires this trigger whenever one of these statements is issued:

salary_check is a BEFORE row trigger, so the database 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 condition that prevents it from checking the salary of the administrative vice president (AD_VP).

Calling a Procedure in a Trigger Body: Example You can 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 check_sal in the hr schema, which verifies that an employee's salary is in an appropriate range. Then you can create the trigger salary_check as follows:

CREATE TRIGGER salary_check
   BEFORE INSERT OR UPDATE OF salary, job_id ON employees
   FOR EACH ROW
   WHEN (new.job_id <> 'AD_VP')
   CALL check_sal(:new.job_id, :new.salary, :new.last_name)

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

Creating a Database Event Trigger: Example This example shows the basic syntax for a trigger to log all errors. The hypothetical 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;

Creating an INSTEAD OF Trigger: Example In this example, an oe.order_info view is created to display information about customers and their orders:

CREATE VIEW order_info AS
   SELECT c.customer_id, c.cust_last_name, c.cust_first_name,
          o.order_id, o.order_date, o.order_status
   FROM customers c, orders o 
   WHERE c.customer_id = o.customer_id;

Normally this view is not updatable, because the primary key of the orders table (order_id) is not unique in the result set of the join view. To make this view updatable, create an INSTEAD OF trigger on the view to process INSERT statements directed to the view.

CREATE OR REPLACE TRIGGER order_info_insert
   INSTEAD OF INSERT ON order_info
   DECLARE
     duplicate_info EXCEPTION;
     PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
   BEGIN
     INSERT INTO customers
       (customer_id, cust_last_name, cust_first_name) 
     VALUES (
     :new.customer_id, 
     :new.cust_last_name,
     :new.cust_first_name);
   INSERT INTO orders (order_id, order_date, customer_id)
   VALUES (
     :new.order_id,
     :new.order_date,
     :new.customer_id);
   EXCEPTION
     WHEN duplicate_info THEN
       RAISE_APPLICATION_ERROR (
         num=> -20107,
         msg=> 'Duplicate customer or order ID');
   END order_info_insert;
/

You can now insert into both base tables through the view (if all NOT NULL columns receive values):

INSERT INTO order_info VALUES
   (999, 'Smith', 'John', 2500, '13-MAR-2001', 0);

For more information about INSTEAD OF triggers, see "INSTEAD OF Triggers".

Creating a SCHEMA Trigger: Example This example creates a BEFORE statement trigger on the sample schema hr. When a user connected as hr attempts to drop a database object, the database fires the trigger before dropping the object:

CREATE OR REPLACE TRIGGER drop_trigger 
   BEFORE DROP ON hr.SCHEMA 
   BEGIN
      RAISE_APPLICATION_ERROR (
         num => -20000,
         msg => 'Cannot drop object');
   END;
/

Related Topics

In this chapter:

In other chapters:

See Also:

Oracle Database Advanced Application Developer's Guide for more information about crossedition triggers
Go to previous page
Previous		 Go to next page
Next
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