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
BEGIN
  triggered_action;
END;

The parameters are described as follows:

• 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

Condition predicates

A condition predicate is used in a trigger to detect the condition that triggers the DML statement, so as to determine the logic to be executed by the trigger.

• UPDATING: determines whether an UPDATE operation is performed. When the IF UPDATING THEN statement is used in a trigger, the condition is TRUE if an UPDATE operation is triggered. In this case, the trigger can execute specific logic to execute the UPDATE operation.
• UPDATING('column_name'): determines whether an UPDATE operation is performed on the column specified by column_name. When the IF UPDATING('column_name') THEN statement is used in a trigger, the condition is TRUE if an UPDATE operation is triggered and the specified column is updated. In this case, the trigger can execute specific logic to update the specified column.
• INSERTING: determines whether an INSERT operation is performed. When the IF INSERTING THEN statement is used in a trigger, the condition is TRUE if an INSERT operation is triggered. In this case, the trigger can execute specific logic to execute the INSERT operation.
• DELETING: determines whether a DELETE operation is performed. When the IF DELETING THEN statement is used in a trigger, the condition is TRUE if a DELETE operation is triggered. In this case, the trigger can execute specific logic to execute the DELETE operation.

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

Example 3: Create a trigger to record the update events in another table when certain columns in a table are updated.

1. Create a table named tbl1, which is monitored by the trigger.

   CREATE TABLE tbl1(col1 INT, col2 VARCHAR2(20));
   

2. Insert a data record into the tbl1 table.

   INSERT INTO tbl1(col1, col2) VALUES(1,'A1');
   

3. Create a log table named tbl2, which is used by the trigger to record specific events.

   CREATE TABLE tbl2(col1 VARCHAR2(255));
   

4. Change the end character to //.

   DELIMITER //
   

5. Create a trigger named update_tbl1_trigger to monitor the update operations on the col1 and col2 columns in the tbl1 table and insert corresponding information into the tbl2 table based on the update operation performed. The logic of the trigger is as follows:

   • If the col1 and col2 columns of any row are both updated, the "Updating both col1 and col2!" message is inserted into the tbl2 table.
   • If only col1 is updated, the "Updating only col1!" message is inserted into the tbl2 table.
   • If only col2 is updated, the "Updating only col2!" message is inserted into the tbl2 table.

   CREATE OR REPLACE TRIGGER update_tbl1_trigger
   BEFORE UPDATE OF col1, col2 ON tbl1
   FOR EACH ROW
   BEGIN
      IF UPDATING('col1') AND UPDATING('col2') THEN
         INSERT INTO tbl2(col1) VALUES ('Updating both col1 and col2!');
      END IF;
      IF UPDATING('col1') AND NOT UPDATING('col2') THEN
         INSERT INTO tbl2(col1) VALUES ('Updating only col1!');
      END IF;
      IF UPDATING('col2') AND NOT UPDATING('col1') THEN
         INSERT INTO tbl2(col1) VALUES ('Updating only col2!');
      END IF;
   END;
   //
   

6. Change the end character back to ;.

   DELIMITER ;
   

7. Update the col2 value where col1 = 1 in the tbl1 table to 'A11'.

   UPDATE tbl1 SET col2 = 'A11' WHERE col1 = 1;
   

8. Query data in the tbl2 table.

   SELECT * FROM tbl2;
   

   The return result is as follows:

   +---------------------+
   | COL1                |
   +---------------------+
   | Updating only col2! |
   +---------------------+
   1 row in set