CREATE TRIGGER

You can use the CREATE TRIGGER statement to create or replace a database trigger.

When specified conditions are met, the database automatically runs the trigger.

Prerequisites

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

To create a trigger in the schema (schema.SCHEMA) of other users, you must have the CREATE ANY TRIGGER system privilege.

If a trigger is used to execute an SQL statement or call a stored procedure or function, the owner of the trigger must have the privileges required to perform these operations. These privileges must be granted to the owner directly rather than through roles.

Syntax

The syntax of create_trigger is as follows:

CREATE [ OR REPLACE ]
TRIGGER plsql_trigger_source

Specifically:

• 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, which fires after another trigger named 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