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

Events include:

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

Where trigger_order is:

    { FOLLOWS|PRECEDES } other_trigger_name

Parameter Description

  • CONSTRAINT

    (Optional) Creates a constraint trigger. That is, the trigger is used as a constraint. Such a trigger is similar to 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 a trigger function is called after the trigger 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.

  • 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 a constraint. This parameter can be specified only 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. Controls whether the constraint can be deferred.

    For details, see CREATE TABLE.

  • INITIALLY IMMEDIATE | INITIALLY DEFERRED

    If a constraint is deferrable, the two clauses specify the default time to check the constraint, and can be specified only 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 data of trigger type. This function is executed when a trigger fires.

  • arguments

    Specifies an optional, comma-separated list of parameters to be provided to a function when a trigger is executed. Parameters are literal string constants. Simple names and numeric constants can also be included, but they will all be converted to strings. Check descriptions of the implementation language of a trigger function to find out how these parameters are 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.

    It can also be a single SQL statement. Currently, the following statements are supported: INSERT, UPDATE, DELETE, SET, and CALL.

    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.

    Table 1 Types of triggers supported on tables and views

    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. This variable is NULL for DELETE operations.

    TG_WHEN

    Trigger timing (BEFORE/AFTER/INSTEAD OF). This variable is NULL for DELETE operations.

    TG_LEVEL

    Trigger frequency (ROW/STATEMENT). This variable is NULL for DELETE operations.

    TG_OP

    Trigger event (INSERT/UPDATE/DELETE/TRUNCATE). This variable is NULL for DELETE operations.

    TG_RELID

    OID of the table where the trigger resides. This variable is NULL for DELETE operations.

    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. This variable is NULL for DELETE operations.

    TG_TABLE_SCHEMA

    Schema of the table where the trigger resides. This variable is NULL for DELETE operations.

    TG_NARGS

    Number of parameters for the trigger function. This variable is NULL for DELETE operations.

    TG_ARGV[]

    List of parameters for the trigger function. This variable is NULL for DELETE operations.

Examples

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

--Create a trigger function.
openGauss=# 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;

openGauss=# 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;

openGauss=# 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.
openGauss=# CREATE TRIGGER insert_trigger
           BEFORE INSERT ON test_trigger_src_tbl
           FOR EACH ROW
           EXECUTE PROCEDURE tri_insert_func();

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

--Create a DELETE trigger.
openGauss=# 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.
openGauss=# INSERT INTO test_trigger_src_tbl VALUES(100,200,300);
openGauss=# SELECT * FROM test_trigger_src_tbl;
openGauss=# SELECT * FROM test_trigger_des_tbl;  // Check whether the trigger operation takes effect.

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

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

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

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

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

--Delete triggers.
openGauss=# DROP TRIGGER insert_trigger ON test_trigger_src_tbl;
openGauss=# DROP TRIGGER update_trigger ON test_trigger_src_tbl;
openGauss=# DROP TRIGGER delete_trigger_renamed ON test_trigger_src_tbl;
--Create a MySQL-compatible database.
openGauss=# create database db_mysql dbcompatibility 'B';
--Create a trigger definition user.
openGauss=# 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, and ALTER TABLE

Feedback
编组 3备份
    openGauss 2024-10-03 04:45:44
    cancel