CREATE TRIGGER

The CREATE TRIGGER statement is used to create or replace a database trigger.

The database automatically runs the trigger when the specified condition is met.

Prerequisites

To create a trigger on your own schema or on a table in a schema, you must have the CREATE TRIGGER system privilege.

To create a trigger in another user's schema (schema.SCHEMA), you must have the CREATE ANY TRIGGER system privilege.

If the trigger executes SQL statements or calls stored procedures or functions, the trigger owner must have the privileges required to perform these actions. These privileges must be granted directly to the user, not through a role.

Syntax

The syntax of create_trigger is as follows:

CREATE [ OR REPLACE ]
TRIGGER plsql_trigger_source

where:

• The syntax of plsql_trigger_source is as follows:

  [schema.] trigger_name
    { simple_dml_trigger
     | instead_of_dml_trigger
     | trigger_ordering_clause
   }
  

• The syntax of simple_dml_trigger is as follows:

  { BEFORE | AFTER } dml_event_clause [ referencing_clause ] [ FOR EACH ROW ]
  [ ENABLE | DISABLE ] [ WHEN ( condition ) ] trigger_body
  

• The syntax of instead_of_dml_trigger is as follows:

  INSTEAD OF { DELETE | INSERT | UPDATE } [ OR { DELETE | INSERT | UPDATE } ]...
  ON [ schema. ] view
  [ referencing_clause ] [ FOR EACH ROW ]
  [ ENABLE | DISABLE ] trigger_body
  

• The syntax of trigger_ordering_clause is as follows:

  { FOLLOWS | PRECEDES } [ schmema.] trigger_name [ , [ schmema.] trigger_name ]...
  

• The syntax of dml_event_clause is as follows:

  { DELETE | INSERT | UPDATE [ OF column [, column ]... ] }
  [ OR { DELETE | INSERT | UPDATE [ OF column [, column]... ] }...]
  ON [ schema.] { table | view }
  

• The syntax of referencing_clause is as follows:

  REFERENCING
  { OLD [ AS ] old
  | NEW [ AS ] new
  | PARENT [ AS ] parent
  }..
  

• The syntax of trigger_body is as follows:

  { plsql_block | CALL routine_clause }
  

Semantics

Examples

Example 1: Create a DML trigger named del_new_region.

CREATE OR REPLACE TRIGGER del_new_region
      BEFORE DELETE ON regions
      FOR EACH ROW
      WHEN (old.region_id >3)
   BEGIN
      INSERT INTO reg_his(region_id , region_name )
           VALUES( :old.region_id, :old.region_name );
   END;
 /

Example 2: Create a trigger named tri2_t that fires after the trigger tri1_t.

CREATE SEQUENCE seq_tri INCREMENT BY 1 START WITH 1 NOMAXVALUE MINVALUE 0 NOCYCLE NOCACHE;
CREATE TABLE t(c1 INT, c2 INT);
CREATE TABLE msg (c1 INT PRIMARY KEY, c2 VARCHAR(100));
CREATE OR REPLACE TRIGGER tri1_t BEFORE INSERT ON t FOR EACH ROW
BEGIN
  INSERT INTO msg VALUES (seq_tri.NEXTVAL, 'BEFORE INSERT tri1_t');
END;
/

CREATE OR REPLACE TRIGGER tri2_t BEFORE INSERT ON t FOR EACH ROW FOLLOWS tri1_t
BEGIN
  INSERT INTO msg VALUES (seq_tri.nextval, 'BEFORE INSERT tri2_t');
END;
/

INSERT INTO t VALUES (1, 1);

obclient> SELECT * FROM msg;
+----+----------------------+
| C1 | C2                   |
+----+----------------------+
|  1 | BEFORE INSERT tri1_t |
|  2 | BEFORE INSERT tri2_t |
+----+----------------------+
2 rows in set