CREATE TRIGGER

Function

• Creates a trigger. The trigger will be associated with the specified table or view and will execute the specified function under certain conditions.

• New syntax for creating triggers using MySQL formatting compared to the original MogDB syntax.

• Added syntax for creating triggers using a single sql.

Precautions

• Currently, you can only create triggers on normal row-stored tables, but not on column-stored tables, temporary tables, unlogged tables, etc. If you define multiple triggers of the same type for the same event, they are triggered in the alphabetical order of their names.
• If multiple triggers of the same type are defined for the same event, they are triggered in alphabetical order of the trigger name.
• Triggers are often used in synchronization scenarios of data association between multiple tables, which have a big impact on SQL execution performance, and are not recommended to be used in synchronization scenarios with large data volume and high performance requirements.
• The user who creates the trigger needs to have the TRIGGER privilege of the specified table or be granted the CREATE ANY TRIGGER privilege.

Syntax

• Syntax for Oracle style trigger creation

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

• Syntax for creating triggers in mysql-compatible style

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

Where event contains the following:

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

Where trigger_order is:

    { FOLLOWS|PRECEDES } other_trigger_name

Parameter Description

• CONSTRAINT

  Optional, specifying this parameter creates a constraint trigger, i.e. the trigger is used as a constraint. This is the same as for regular triggers except that you can use SET CONSTRAINTS to adjust the time at which the trigger is triggered. The constraint trigger must be an AFTER ROW trigger.

• DEFINER

  Optional, specify this parameter to affect the permission control of the referenced objects within the trigger.

• IF NOT EXISTS

  Optional, specify this parameter to prevent an error from occurring if the trigger has the same name, the same table, and the same table in the same schema.

• trigger_name

  The name of the trigger, which cannot qualify the schema because triggers automatically inherit the schema of the table in which they reside, and triggers from the same table cannot be renamed. For constrained triggers, this name is also used when using SET CONSTRAINTS to modify the trigger behavior.

  Range of values: a string that conforms to the identifier naming convention and has a maximum length of 63 characters.

• BEFORE

  Trigger functions are executed before the trigger event occurs.

• AFTER

  Trigger functions are executed after a trigger event occurs, and constraint triggers can only be specified as AFTER.

• INSTEAD OF

  Trigger functions are direct substitutes for triggering events.

• event

  The event that initiates the trigger, with values ranging from: INSERT, UPDATE, DELETE, or TRUNCATE, or you can specify more than one trigger event at the same time via OR.

  For the UPDATE event type, columns can be specified using the following syntax:

  UPDATE OF column_name1 [, column_name2 ... ]

  Indicates that the trigger is fired when these columns are used as the target columns of the UPDATE statement, but the INSTEAD OF UPDATE type does not support specifying column information.

• table_name

  The name of the table where the trigger needs to be created.

  Range of values: the name of a table that already exists in the database.

• referenced_table_name

  The name of the other table referenced by the constraint. Can only be specified for constraint triggers, commonly used for foreign key constraints.

  Range of values: the name of a table that already exists in the database.

• DEFERRABLE | NOT DEFERRABLE

  The timing of the start of a constraint trigger acts only on constraint triggers. These two keywords set whether the constraint is deferrable.

  For details, please refer to CREATE TABLE.

• INITIALLY IMMEDIATE | INITIALLY DEFERRED

  If the constraint is deferrable, this clause declares the default time to check the constraint, acting only on constraint triggers.

  For details, please refer to CREATE TABLE.

• FOR EACH ROW | FOR EACH STATEMENT

  Trigger frequency of the trigger.

  • FOR EACH ROW means that the trigger is triggered once for each row affected by the trigger event.
  • FOR EACH STATEMENT means that the trigger is triggered only once per SQL statement.

  The default value is FOR EACH STATEMENT when not specified. constraint triggers can only be specified as FOR EACH ROW.

• condition

  A conditional expression that determines whether the trigger function is actually executed. When WHEN is specified, the function is called only if the condition returns true.

  In FOR EACH ROW triggers, the WHEN condition can reference columns with old or new row values by writing to OLD.column_name or NEW.column_name, respectively. Of course, INSERT triggers cannot reference OLD and DELETE triggers cannot reference NEW.

  INSTEAD OF triggers do not support WHEN conditions.

  WHEN expressions cannot contain subqueries.

  For constrained triggers, the evaluation of the WHEN condition is not delayed, but occurs immediately after the update operation is performed. If the condition returns a value other than TRUE, the trigger is not queued for delayed execution.

• function_name

  User-defined functions, which must be declared without parameters and with a return type of trigger, are executed when the trigger is triggered.

• arguments

  An optional comma-separated list of arguments to be supplied to the function when executing the trigger. The parameters are literal string constants; simple name 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 see how to access these parameters within the function.

• trigger_order

  Optionally, the {FOLLOWS|PRECEDES} in the trigger_order feature controls the trigger priority order. B-compatibility mode allows multiple triggers to be defined for the same table, on the same triggering event, and the priority of triggering is determined according to the order in which the triggers are created (the first one to be created takes precedence). The priority can be adjusted by {FOLLOWS|PRECEDES}. With FOLLOWS, the last used trigger is closest to the original trigger, and all other triggers are squeezed backward in priority order; with PRECEDES, the last used trigger is closest to the original trigger, and all other triggers are squeezed forward in priority order.

• trigger_body

  Define the work to be done after the trigger by writing a block of code directly between begin.... . end to define the work to be done after the trigger.

  It can also be a single sql statement, currently supported statements: insert, update, delete, set, call.

  When the separator is set, using the MySQL style syntax for creating triggers, the trigger_body is formatted according to MySQL's formatting rules, and the declare paragraph needs to be written between the begin ... end paragraphs.

  Note:

  About trigger types:

  • INSTEAD OF triggers must be labeled FOR EACH ROW and can only be defined on views.
  • BEFORE and AFTER triggers can only be labeled FOR EACH STATEMENT when acting on a view.
  • TRUNCATE type triggers are limited to FOR EACH STATEMENT.

  Table 1 Types of triggers supported on tables and views:

  Trigger timing	Trigger Event	Row-level	Statement-level
  BEFORE	INSERT/UPDATE/DELETE	Table	Table and views
  	TRUNCATE	not support	Table
  AFTER	INSERT/UPDATE/DELETE	Table	Table and views
  	TRUNCATE	not support	Table
  INSTEAD OF	INSERT/UPDATE/DELETE	Views	not support
  	TRUNCATE	not support	not support

  Table 2 PLPGSQL Type Trigger Function Special Variables:

  Variable name	Variable Meaning
  NEW	INSERT and UPDATE operations involve new values in the tuple information and are null for DELETE.
  OLD	UPDATE and DELETE operations involve old values in the tuple information and are empty for INSERT.
  TG_NAME	Trigger name.
  TG_WHEN	Trigger timing (BEFORE/AFTER/INSTEAD OF).
  TG_LEVEL	Trigger frequency (ROW/STATEMENT).
  TG_OP	Trigger operations (INSERT/UPDATE/DELETE/TRUNCATE).
  TG_RELID	OID of the table where the trigger is located.
  TG_RELNAME	The name of the table where the trigger is located (deprecated and now replaced by TG_TABLE_NAME).
  TG_TABLE_NAME	The name of the table where the trigger is located.
  TG_TABLE_SCHEMA	SCHEMA information for the table where the trigger is located.
  TG_NARGS	Number of trigger function arguments.
  TG_ARGV[]	Trigger function parameter list.

Examples

-- Creating a source table and trigger 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);

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

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

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

--  Creates an 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 trigger event and check the trigger result
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 if the triggered action takes effect.

-- Execute the UPDATE trigger event and check the trigger result
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 if the triggered action takes effect

-- Execute DELETE to trigger the event and check the trigger result
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 if the triggered action takes effect

-- Modify Trigger
MogDB=# ALTER TRIGGER delete_trigger ON test_trigger_src_tbl RENAME TO delete_trigger_renamed;

-- Disable insert_trigger 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 a Trigger
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 B-compatible database
MogDB=# create database db_mysql dbcompatibility 'B';
-- Create a trigger to define the user.
MogDB=# create user test_user password 'Gauss@123';
-- Create the original table and trigger 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 MySQL compatible definer syntax trigger.
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 MySQL compatible trigger_order syntax trigger.
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 MySQL compatible FOLLOWS trigger
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 trigger event and check the 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 MySQL compatible PROCEDES trigger
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 trigger event and check the 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 MySQL compatible trigger with 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;
          /
-- Create MySQL format trigger.
db_mysql=# delimiter //

db_mysql=# create trigger animal_d_trigger1
          after insert on animals
          for each row
          begin
              insert into food (id ,foodtype, remark, time_flag) values(1,'ice','avcs', now());
          end;
          //

db_mysql=# delimiter ;
-- Create MySQL compatible trigger_body for single SQL syntax trigger.
db_mysql=# create trigger animal_trigger_single
          after insert on animals
          for each row
          insert into food(id, foodtype, remark, time_flag) values (1,'ice cream', 'sdsdsdsd', now());

Helpful Links

ALTER TRIGGER, DROP TRIGGER, ALTER TABLE