CREATE TRIGGER

Function

CREATE TRIGGER creates a trigger. The trigger will be associated with the specified table or view, and will execute the specified function operations are performed.

Precautions

• Currently, triggers can be created only on ordinary row-store tables, instead of on column-store tables, temporary tables, or unlogged tables.
• If multiple triggers of the same kind are defined for the same event, they will be fired in alphabetical order by name.
• Triggers are usually used for data association and synchronization between multiple tables. SQL execution performance is greatly affected. Therefore, you are advised not to use this statement when a large amount of data needs to be synchronized and performance requirements are high.
• To create a trigger, you must have the TRIGGER permission on the specified table or have the CREATE ANY TRIGGER permission.

Syntax

• O-compatible syntax for creating a trigger

  CreateTrigger ::= CREATE [ CONSTRAINT ] TRIGGER trigger_name { BEFORE | AFTER | INSTEAD OF } { event [ OR '...'] }
      ON table_name
      [ FROM referenced_table_name ]
      { NOT DEFERRABLE | [ DEFERRABLE ] { INITIALLY IMMEDIATE | INITIALLY DEFERRED } }
      [ FOR [ EACH ] { ROW | STATEMENT } ]
      [ WHEN ( condition ) ]
      EXECUTE PROCEDURE function_name ( arguments );

• MySQL-compatible syntax for creating a trigger

  CreateTrigger ::= CREATE [ CONSTRAINT ] [ DEFINER = user] TRIGGER [ IF NOT EXISTS ] trigger_name { BEFORE | AFTER | INSTEAD OF } { event [ OR '...' ] }
      ON table_name
      [ FROM referenced_table_name ]
      { NOT DEFERRABLE | [ DEFERRABLE ] { INITIALLY IMMEDIATE | INITIALLY DEFERRED } }
      [ FOR [ EACH ] { ROW | STATEMENT } ]
      [ WHEN ( condition ) ]
      [ trigger_order ]
      trigger_body

  Event include:

     INSERT
     UPDATE [ OF column_name [, ... ] ]
     DELETE
     TRUNCATE

  Where trigger_order is:

   trigger_order ::=  { FOLLOWS|PRECEDES } other_trigger_name

Parameter Description

• CONSTRAINT

  (Optional) Creates a constraint trigger. That is, the trigger is used as a constraint. This is the same as a regular trigger except that the timing of the trigger firing can be adjusted using SET CONSTRAINTS. Constraint triggers must be AFTER ROW triggers.

• DEFINER

  (Optional) Affects the permission control of the referenced object within the trigger.

• IF NOT EXISTS

  (Optional) Prevents errors if triggers have the same name, table, or table in the same schema.

• trigger_name

  Specifies the name of the trigger to be created. This must be distinct from the name of any other trigger for the same table. The name cannot be schema-qualified - the trigger inherits the schema of its table. For a constraint trigger, this is also the name to use when modifying the trigger's behavior using SET CONSTRAINTS.

  Value range: a string, which complies with the identifier naming convention and contains a maximum of 63 characters.

• BEFORE

  Specifies that the function is called before the event.

• AFTER

  Specifies that the function is called after the event. A constraint trigger can only be specified as AFTER.

• INSTEAD OF

  Specifies that the function is called instead of the event.

• event

  Specifies the event that will fire the trigger. Values are INSERT, UPDATE, DELETE, and TRUNCATE. Multiple events can be specified using OR.

  For UPDATE events, it is possible to specify a list of columns using this syntax:

  UPDATE OF column_name1 [, column_name2 ... ]

  The trigger will only fire if at least one of the listed columns is mentioned as a target of the UPDATE statement. INSTEAD OF UPDATE events do not support lists of columns. If the column specified by UPDATE OF contains a generated column, the trigger is also fired when the column on which the generated column depends is the target column of the UPDATE statement.

• table_name

  Specifies the name of the table for which the trigger is created.

  Value range: name of an existing table in the database

• referenced_table_name

  Specifies the name of another table referenced by the constraint. This option is used for foreign-key constraints. It can only be specified for constraint triggers.

  Value range: name of an existing table in the database

• DEFERRABLE | NOT DEFERRABLE

  Specifies the start time of the trigger. It can only be specified for constraint triggers. They determine whether the constraint is deferrable.

  For details, see CREATE TABLE.

• INITIALLY IMMEDIATE | INITIALLY DEFERRED

  If the constraint is deferrable, the two clauses specify the default time to check the constraint. It can only be specified for constraint triggers.

  For details, see CREATE TABLE.

• FOR EACH ROW | FOR EACH STATEMENT

  Specifies the frequency of firing the trigger.

  • FOR EACH ROW indicates that the trigger should be fired once for every row affected by the trigger event.

  • FOR EACH STATEMENT indicates that the trigger should be fired just once per SQL statement.

    If neither is specified, the default is FOR EACH STATEMENT. Constraint triggers can only be marked as FOR EACH ROW.

• condition

  Specifies whether the trigger function will actually be executed. If WHEN is specified, the function will be called only when condition returns true.

  In FOR EACH ROW triggers, the WHEN condition can refer to columns of the old and/or new row values by writing OLD.column name or NEW.column name respectively. In addition, INSERT triggers cannot refer to OLD, and DELETE triggers cannot refer to NEW.

  INSTEAD OF triggers do not support WHEN conditions.

  Currently, WHEN expressions cannot contain subqueries.

  Note that for constraint triggers, evaluation of the WHEN condition is not deferred, but occurs immediately after the row update operation is performed. If the condition does not evaluate to true, then the trigger is not queued for deferred execution.

• function_name

  Specifies a user-defined function, which must be declared as taking no parameters and returning type trigger. This is executed when a trigger fires.

• arguments

  Specifies an optional comma-separated list of parameters to be provided to the function when the trigger is executed. The parameters are literal string constants. Simple names and numeric constants can also be written here, but they will all be converted to strings. Check the description of the implementation language of the trigger function to find out how these parameters can be accessed within the function.

• trigger_order

  (Optional) {FOLLOWS|PRECEDES} in the trigger_order feature controls the triggering sequence. In MySQL-compatible mode, multiple triggers can be defined for the same triggered event in the same table. The trigger that is created first is triggered first. You can use {FOLLOWS|PRECEDES} to adjust the priority. If FOLLOWS is used, the trigger used for the last time has the highest priority. If PRECEDES is used, the trigger used for the last time has the lowest priority.

• trigger_body

  Define the work to be done after the trigger by writing a code block between BEGIN and END.

  Table 1 Types of triggers supported on tables and views

  NOTE: The following details trigger types:

  • INSTEAD OF triggers must be marked as FOR EACH ROW and can be defined only on views.
  • BEFORE and AFTER triggers on a view must be marked as FOR EACH STATEMENT.
  • TRUNCATE triggers must be marked as FOR EACH STATEMENT.

  When	Event	Row-Level	Statement-Level
  BEFORE	INSERT/UPDATE/DELETE	Tables	Tables and views
  TRUNCATE	Not supported.	Tables
  AFTER	INSERT/UPDATE/DELETE	Tables	Tables and views
  TRUNCATE	Not supported.	Tables
  INSTEAD OF	INSERT/UPDATE/DELETE	Views	Not supported.
  TRUNCATE	Not supported.	Not supported.

  Table 2 Special variables in PL/pgSQL functions

  Variable	Description
  NEW	New tuple for INSERT and UPDATE operations. This variable is NULL for DELETE operations.
  OLD	Old tuple for UPDATE and DELETE operations. This variable is NULL for INSERT operations.
  TG_NAME	Trigger name.
  TG_WHEN	Trigger timing (BEFORE, AFTER, or INSTEAD OF).
  TG_LEVEL	Trigger frequency (ROW or STATEMENT).
  TG_OP	Trigger event (INSERT, UPDATE, DELETE, or TRUNCATE).
  TG_RELID	OID of the table where the trigger resides.
  TG_RELNAME	Name of the table where the trigger resides. (This variable has been replaced by TG_TABLE_NAME.)
  TG_TABLE_NAME	Name of the table where the trigger resides.
  TG_TABLE_SCHEMA	Schema of the table where the trigger resides.
  TG_NARGS	Number of parameters for the trigger function.
  TG_ARGV[]	List of parameters for the trigger function.

Examples

-- Create a source table and a destination table.
MogDB=# CREATE TABLE test_trigger_src_tbl(id1 INT, id2 INT, id3 INT);
MogDB=# CREATE TABLE test_trigger_des_tbl(id1 INT, id2 INT, id3 INT);

-- Create a trigger function.
MogDB=# CREATE OR REPLACE FUNCTION tri_insert_func() RETURNS TRIGGER AS
           $$
           DECLARE
           BEGIN
                   INSERT INTO test_trigger_des_tbl VALUES(NEW.id1, NEW.id2, NEW.id3);
                   RETURN NEW;
           END
           $$ LANGUAGE PLPGSQL;

MogDB=# CREATE OR REPLACE FUNCTION tri_update_func() RETURNS TRIGGER AS
           $$
           DECLARE
           BEGIN
                   UPDATE test_trigger_des_tbl SET id3 = NEW.id3 WHERE id1=OLD.id1;
                   RETURN OLD;
           END
           $$ LANGUAGE PLPGSQL;

MogDB=# CREATE OR REPLACE FUNCTION TRI_DELETE_FUNC() RETURNS TRIGGER AS
           $$
           DECLARE
           BEGIN
                   DELETE FROM test_trigger_des_tbl WHERE id1=OLD.id1;
                   RETURN OLD;
           END
           $$ LANGUAGE PLPGSQL;

-- Create an INSERT trigger.
MogDB=# CREATE TRIGGER insert_trigger
           BEFORE INSERT ON test_trigger_src_tbl
           FOR EACH ROW
           EXECUTE PROCEDURE tri_insert_func();

-- Create an UPDATE trigger.
MogDB=# CREATE TRIGGER update_trigger
           AFTER UPDATE ON test_trigger_src_tbl
           FOR EACH ROW
           EXECUTE PROCEDURE tri_update_func();

-- Create a DELETE trigger.
MogDB=# CREATE TRIGGER delete_trigger
           BEFORE DELETE ON test_trigger_src_tbl
           FOR EACH ROW
           EXECUTE PROCEDURE tri_delete_func();

-- Execute the INSERT event and check the trigger results.
MogDB=# INSERT INTO test_trigger_src_tbl VALUES(100,200,300);
MogDB=# SELECT * FROM test_trigger_src_tbl;
MogDB=# SELECT * FROM test_trigger_des_tbl;  //Check whether the trigger operation takes effect.

-- Execute the UPDATE event and check the trigger results.
MogDB=# UPDATE test_trigger_src_tbl SET id3=400 WHERE id1=100;
MogDB=# SELECT * FROM test_trigger_src_tbl;
MogDB=# SELECT * FROM test_trigger_des_tbl;  //Check whether the trigger operation takes effect.

-- Execute the DELETE event and check the trigger results.
MogDB=# DELETE FROM test_trigger_src_tbl WHERE id1=100;
MogDB=# SELECT * FROM test_trigger_src_tbl;
MogDB=# SELECT * FROM test_trigger_des_tbl;  //Check whether the trigger operation takes effect.

-- Modify a trigger.
MogDB=# ALTER TRIGGER delete_trigger ON test_trigger_src_tbl RENAME TO delete_trigger_renamed;

-- Disable insert_trigger.
MogDB=# ALTER TABLE test_trigger_src_tbl DISABLE TRIGGER insert_trigger;

-- Disable all triggers on the current table.
MogDB=# ALTER TABLE test_trigger_src_tbl DISABLE TRIGGER ALL;

-- Delete triggers.
MogDB=# DROP TRIGGER insert_trigger ON test_trigger_src_tbl;
MogDB=# DROP TRIGGER update_trigger ON test_trigger_src_tbl;
MogDB=# DROP TRIGGER delete_trigger_renamed ON test_trigger_src_tbl;
--Create a MySQL-compatible database.
MogDB=# create database db_mysql dbcompatibility 'B';
--Create a trigger definition user.
MogDB=# create user test_user password 'Gauss@123';
--Create a source table and a target table.
db_mysql=# create table test_mysql_trigger_src_tbl (id INT);
db_mysql=# create table test_mysql_trigger_des_tbl (id INT);
db_mysql=# create table animals (id INT, name CHAR(30));
db_mysql=# create table food (id INT, foodtype VARCHAR(32), remark VARCHAR(32), time_flag TIMESTAMP);
--Create a MySQL trigger that is compatible with the definer syntax.
db_mysql=# create definer=test_user trigger trigger1
after insert on test_mysql_trigger_src_tbl
for each row
begin 
insert into test_mysql_trigger_des_tbl values(1);
end;
/
--Create a MySQL trigger that is compatible with the trigger_order syntax.
db_mysql=# create trigger animal_trigger1
after insert on animals
for each row
begin
insert into food(id, foodtype, remark, time_flag) values (1,'ice cream', 'sdsdsdsd', now());
end;
/
--Create a MySQL trigger that is compatible with FOLLOWS.
db_mysql=# create trigger animal_trigger2
after insert on animals
for each row
follows animal_trigger1
begin
insert into food(id, foodtype, remark, time_flag) values (2,'chocolate', 'sdsdsdsd', now());
end;
/
db_mysql=# create trigger animal_trigger3
          after insert on animals
          for each row
          follows animal_trigger1
          begin
              insert into food(id, foodtype, remark, time_flag) values (3,'cake', 'sdsdsdsd', now());
          end;
          /
db_mysql=# create trigger animal_trigger4
          after insert on animals
          for each row
          follows animal_trigger1
          begin
              insert into food(id, foodtype, remark, time_flag) values (4,'sausage', 'sdsdsdsd', now());
          end;
          /
--Execute the INSERT statement to trigger an event and check the trigger result:
db_mysql=# insert into animals (id, name) values(1,'lion');
db_mysql=# select * from animals;
db_mysql=# select id, foodtype, remark from food;
--Create a MySQL trigger that is compatible with PROCEDES.
db_mysql=# create trigger animal_trigger5
          after insert on animals
          for each row
          precedes animal_trigger3
          begin
              insert into food(id, foodtype, remark, time_flag) values (5,'milk', 'sdsds', now());
          end;
          /
db_mysql=# create trigger animal_trigger6
          after insert on animals
          for each row
          precedes animal_trigger2
          begin
              insert into food(id, foodtype, remark, time_flag) values (6,'strawberry', 'sdsds', now());
          end;
          /
--Execute the INSERT statement to trigger an event and check the trigger result:
db_mysql=# insert into animals (id, name) values(2, 'dog');
db_mysql=# select * from animals;
db_mysql=# select id, foodtype, remark from food;
--Create a MySQL trigger that is compatible with the IF NOT EXISTS syntax.
db_mysql=# create trigger if not exists animal_trigger1
          after insert on animals
          for each row
          begin
              insert into food(id, foodtype, remark, time_flag) values (1,'ice cream', 'sdsdsdsd', now());
          end;
          /

Helpful Links

ALTER TRIGGER，DROP TRIGGER，ALTER TABLE