CREATE TRIGGER

    注意事项

    • 当前仅支持在普通行存表上创建触发器，不支持在列存表、临时表、unlogged表等类型表上创建触发器。
    • 如果为同一事件定义了多个相同类型的触发器，则按触发器的名称字母顺序触发它们。
    • 触发器常用于多表间数据关联同步场景，对SQL执行性能影响较大，不建议在大数据量同步及对性能要求高的场景中使用。
    • 执行创建触发器操作的用户需要拥有指定表的TRIGGER权限或被授予了CREATE ANY TRIGGER权限。
    • o风格创建触发器的语法
    • 兼容mysql兼容风格的创建触发器的语法
    2.     ON table_name
    3.     [ FROM referenced_table_name ]
    4.     { NOT DEFERRABLE | [ DEFERRABLE ] { INITIALLY IMMEDIATE | INITIALLY DEFERRED } }
    5.     [ FOR [ EACH ] { ROW | STATEMENT } ]
    6.     [ WHEN ( condition ) ]
    7.     [ trigger_order ]
    8.     trigger_body

    其中event包含以下几种：

    其中trigger_order是：

    1.     { FOLLOWS|PRECEDES } other_trigger_name

    参数说明

    • CONSTRAINT

      可选项，指定此参数将创建约束触发器，即触发器作为约束来使用。除了可以使用SET CONSTRAINTS调整触发器触发的时间之外，这与常规触发器相同。 约束触发器必须是AFTER ROW触发器。

    • DEFINER

      可选项，指定此参数可以影响触发器内引用对象的权限控制。

    • IF NOT EXISTS

      可选项，指定此参数如果触发器在相同的模式中具有相同的名称、相同的表、相同的表，则防止发生错误。

    • trigger_name

      触发器名称，该名称在mysql兼容风格语法中可以指定模式，触发器模式必须与其所在表的模式相同，若不限定模式，触发器自动继承其所在表的模式，同一个表的触发器不能重名。对于约束触发器，使用SET CONSTRAINTS修改触发器行为时也使用此名称。

      取值范围：符合标识符命名规范的字符串，且最大长度不超过63个字符。

    • BEFORE

      触发器函数是在触发事件发生前执行。

    • AFTER

      触发器函数是在触发事件发生后执行，约束触发器只能指定为AFTER。

    • INSTEAD OF

      触发器函数直接替代触发事件。

    • event

      启动触发器的事件，取值范围包括：INSERT、UPDATE、DELETE或TRUNCATE，也可以通过OR同时指定多个触发事件。

      对于UPDATE事件类型，可以使用下面语法指定列：

      表示当这些列作为UPDATE语句的目标列时，才会启动触发器，但是INSTEAD OF UPDATE类型不支持指定列信息。如果UPDATE OF指定的列包含生成列，当生成列依赖的列是UPDATE语句的目标列时，也会启动触发器。

    • table_name

      需要创建触发器的表名称。

      取值范围：数据库中已经存在的表名称。

    • referenced_table_name

      约束引用的另一个表的名称。 只能为约束触发器指定，常见于外键约束。

      取值范围：数据库中已经存在的表名称。

    • DEFERRABLE | NOT DEFERRABLE

      约束触发器的启动时机，仅作用于约束触发器。这两个关键字设置该约束是否可推迟。

      详细介绍请参见。

    • INITIALLY IMMEDIATE | INITIALLY DEFERRED

      如果约束是可推迟的，则这个子句声明检查约束的缺省时间，仅作用于约束触发器。

      详细介绍请参见CREATE TABLE。

    • FOR EACH ROW | FOR EACH STATEMENT

      • FOR EACH ROW是指该触发器是受触发事件影响的每一行触发一次。
      • FOR EACH STATEMENT是指该触发器是每个SQL语句只触发一次。

      未指定时默认值为FOR EACH STATEMENT。约束触发器只能指定为FOR EACH ROW。

    • condition

      决定是否实际执行触发器函数的条件表达式。当指定WHEN时，只有在条件返回true时才会调用该函数。

      在FOR EACH ROW触发器中，WHEN条件可以通过分别写入OLD.column_name或NEW.column_name来引用旧行或新行值的列。 当然，INSERT触发器不能引用OLD和DELETE触发器不能引用NEW。

      INSTEAD OF触发器不支持WHEN条件。

      WHEN表达式不能包含子查询。

      对于约束触发器，WHEN条件的评估不会延迟，而是在执行更新操作后立即发生。 如果条件返回值不为true，则触发器不会排队等待延迟执行。

    • function_name

      用户定义的函数，必须声明为不带参数并返回类型为触发器，在触发器触发时执行。

    • arguments

      执行触发器时要提供给函数的可选的以逗号分隔的参数列表。参数是文字字符串常量，简单的名称和数字常量也可以写在这里，但它们都将被转换为字符串。 请检查触发器函数的实现语言的描述，以了解如何在函数内访问这些参数。

    • trigger_order

      可选项，trigger_order特征中的{FOLLOWS|PRECEDES}控制触发器的优先触发顺序，mysql兼容性模式下允许对同一个表，在同一触发事件定义多个触发器，会按照触发器创建的先后顺序来决定触发的优先顺序（先创建的优先）。可以通过{FOLLOWS|PRECEDES}来调整优先级。使用FOLLOWS，最后一次使用的触发器与原始触发器最紧挨着，其他的触发器的优先级都顺序向后挤压；使用PRECEDES，最后一次使用的触发器与原始触发器最紧挨着，其他的触发器的优先级都顺序向前挤压。

    • trigger_body

      直接通过在begin…end之间书写代码块，定义触发器之后要完成的工作。

      表 1 表和视图上支持的触发器种类：

      表 2 PLPGSQL类型触发器函数特殊变量：

      变量名

      变量含义

      NEW

      INSERT及UPDATE操作涉及tuple信息中的新值，对DELETE为空。

      OLD

      UPDATE及DELETE操作涉及tuple信息中的旧值，对INSERT为空。

      TG_NAME

      触发器名称。

      TG_WHEN

      触发器触发时机（BEFORE/AFTER/INSTEAD OF）。

      TG_LEVEL

      触发频率（ROW/STATEMENT）。

      TG_OP

      触发操作（INSERT/UPDATE/DELETE/TRUNCATE）。

      TG_RELID

      触发器所在表OID。

      TG_RELNAME

      触发器所在表名（已废弃，现用TG_TABLE_NAME替代）。

      TG_TABLE_NAME

      触发器所在表名。

      TG_TABLE_SCHEMA

      触发器所在表的SCHEMA信息。

      TG_NARGS

      触发器函数参数个数。

      TG_ARGV[]

    1. --创建源表及触发表
    2. openGauss=# CREATE TABLE test_trigger_src_tbl(id1 INT, id2 INT, id3 INT);
    3. openGauss=# CREATE TABLE test_trigger_des_tbl(id1 INT, id2 INT, id3 INT);
    5. --创建触发器函数
    6. openGauss=# CREATE OR REPLACE FUNCTION tri_insert_func() RETURNS TRIGGER AS
    7.            $$
    8.            DECLARE
    9.            BEGIN
    10.                    INSERT INTO test_trigger_des_tbl VALUES(NEW.id1, NEW.id2, NEW.id3);
    11.                    RETURN NEW;
    12.            END
    13.            $$ LANGUAGE PLPGSQL;
    15. openGauss=# CREATE OR REPLACE FUNCTION tri_update_func() RETURNS TRIGGER AS
    16.            $$
    17.            DECLARE
    18.            BEGIN
    19.                    UPDATE test_trigger_des_tbl SET id3 = NEW.id3 WHERE id1=OLD.id1;
    20.                    RETURN OLD;
    21.            END
    22.            $$ LANGUAGE PLPGSQL;
    24. openGauss=# CREATE OR REPLACE FUNCTION TRI_DELETE_FUNC() RETURNS TRIGGER AS
    25.            $$
    26.            DECLARE
    27.            BEGIN
    28.                    DELETE FROM test_trigger_des_tbl WHERE id1=OLD.id1;
    29.                    RETURN OLD;
    30.            END
    31.            $$ LANGUAGE PLPGSQL;
    33. openGauss=# CREATE TRIGGER insert_trigger
    34.            BEFORE INSERT ON test_trigger_src_tbl
    35.            FOR EACH ROW
    36.            EXECUTE PROCEDURE tri_insert_func();
    38. --创建UPDATE触发器
    39. openGauss=# CREATE TRIGGER update_trigger
    40.            AFTER UPDATE ON test_trigger_src_tbl  
    41.            FOR EACH ROW
    42.            EXECUTE PROCEDURE tri_update_func();
    44. --创建DELETE触发器
    45. openGauss=# CREATE TRIGGER delete_trigger
    46.            BEFORE DELETE ON test_trigger_src_tbl
    47.            FOR EACH ROW
    48.            EXECUTE PROCEDURE tri_delete_func();
    50. --执行INSERT触发事件并检查触发结果
    51. openGauss=# INSERT INTO test_trigger_src_tbl VALUES(100,200,300);
    52. openGauss=# SELECT * FROM test_trigger_src_tbl;
    53. openGauss=# SELECT * FROM test_trigger_des_tbl;  //查看触发操作是否生效。
    55. --执行UPDATE触发事件并检查触发结果
    57. openGauss=# SELECT * FROM test_trigger_src_tbl;
    58. openGauss=# SELECT * FROM test_trigger_des_tbl;  //查看触发操作是否生效
    60. --执行DELETE触发事件并检查触发结果
    61. openGauss=# DELETE FROM test_trigger_src_tbl WHERE id1=100;
    62. openGauss=# SELECT * FROM test_trigger_src_tbl;
    63. openGauss=# SELECT * FROM test_trigger_des_tbl;  //查看触发操作是否生效
    65. --修改触发器
    66. openGauss=# ALTER TRIGGER delete_trigger ON test_trigger_src_tbl RENAME TO delete_trigger_renamed;
    68. --禁用insert_trigger触发器
    69. openGauss=# ALTER TABLE test_trigger_src_tbl DISABLE TRIGGER insert_trigger;  
    71. --禁用当前表上所有触发器
    72. openGauss=# ALTER TABLE test_trigger_src_tbl DISABLE TRIGGER ALL;  
    74. --删除触发器
    75. openGauss=# DROP TRIGGER insert_trigger ON test_trigger_src_tbl;
    76. openGauss=# DROP TRIGGER update_trigger ON test_trigger_src_tbl;
    77. openGauss=# DROP TRIGGER delete_trigger_renamed ON test_trigger_src_tbl;
    78. --创建mysql兼容数据库
    79. openGauss=# create database db_mysql dbcompatibility 'B';
    80. --创建触发器定义用户
    81. openGauss=# create user test_user password 'Gauss@123';
    82. --创建原表及触发表
    83. db_mysql=# create table test_mysql_trigger_src_tbl (id INT);
    84. db_mysql=# create table test_mysql_trigger_des_tbl (id INT);
    85. db_mysql=# create table animals (id INT, name CHAR(30));
    86. db_mysql=# create table food (id INT, foodtype VARCHAR(32), remark VARCHAR(32), time_flag TIMESTAMP);
    87. --创建MySQL兼容definer语法触发器
    88. db_mysql=# create definer=test_user trigger trigger1
    89.                     after insert on test_mysql_trigger_src_tbl
    90.                     for each row
    91.                     begin 
    92.                      insert into test_mysql_trigger_des_tbl values(1);
    93.                     end;
    94.                     /
    95. --创建MySQL兼容trigger_order语法触发器
    96. db_mysql=# create trigger animal_trigger1
    97.                      after insert on animals
    98.                     for each row
    99.                     begin
    100.                      insert into food(id, foodtype, remark, time_flag) values (1,'ice cream', 'sdsdsdsd', now());
    101.                     end;
    102.                     /
    103. --创建MySQL兼容FOLLOWS触发器
    104. db_mysql=# create trigger animal_trigger2
    105.                     after insert on animals
    106.                     for each row
    107.                     follows animal_trigger1
    108.                     begin
    109.                     end;
    110.                     /
    111. db_mysql=# create trigger animal_trigger3
    112.           after insert on animals
    113.           for each row
    114.           follows animal_trigger1
    115.           begin
    116.               insert into food(id, foodtype, remark, time_flag) values (3,'cake', 'sdsdsdsd', now());
    117.           end;
    118.           /
    119. db_mysql=# create trigger animal_trigger4
    121.           for each row
    122.           follows animal_trigger1
    123.           begin
    124.               insert into food(id, foodtype, remark, time_flag) values (4,'sausage', 'sdsdsdsd', now());
    125.           end;
    126.           /
    127. --执行insert触发事件并检查触发结果
    128. db_mysql=# insert into animals (id, name) values(1,'lion');
    129. db_mysql=# select * from animals;
    130. db_mysql=# select id, foodtype, remark from food;
    131. --创建MySQL兼容PROCEDES触发器
    132. db_mysql=# create trigger animal_trigger5
    133.           after insert on animals
    134.           for each row
    135.           precedes animal_trigger3
    136.           begin
    137.               insert into food(id, foodtype, remark, time_flag) values (5,'milk', 'sdsds', now());
    138.           end;
    139.           /
    140. db_mysql=# create trigger animal_trigger6
    141.           after insert on animals
    142.           for each row
    143.           precedes animal_trigger2
    144.           begin
    145.               insert into food(id, foodtype, remark, time_flag) values (6,'strawberry', 'sdsds', now());
    146.           end;
    147.           /
    148. --执行insert触发事件并检查触发结果
    149. db_mysql=# insert into animals (id, name) values(2, 'dog');
    150. db_mysql=# select * from animals;
    151. db_mysql=# select id, foodtype, remark from food;
    152. --创建MySQL兼容if not exists语法触发器
    153. db_mysql=# create trigger if not exists animal_trigger1
    154.           after insert on animals
    155.           for each row
    156.           begin
    157.               insert into food(id, foodtype, remark, time_flag) values (1,'ice cream', 'sdsdsdsd', now());
    158.           end;
    159.           /
    160. --mysql兼容删除触发器语法
    161. db_mysql=# drop trigger animal_trigger1;
    162. db_mysql=# drop trigger if exists animal_trigger1;
    163. --在指定模式下创建、重命名、删除触发器语法，触发器的模式需要与表模式相同
    164. create schema testscm;
    165. create table food (id int, foodtype varchar(32), remark varchar(32), time_flag timestamp);
    166. create table testscm.animals_scm (id int, name char(30));
    167. -- 在指定模式下创建触发器
    168. create trigger testscm.animals_trigger
    169. after insert on testscm.animals_scm
    170. for each row
    171. begin
    172.     insert into food(id, foodtype, remark, time_flag) values (1,'bamboo', 'healthy', now());
    173. end;
    174. /
    175. create trigger if not exists testscm.animals_trigger
    176. after insert on testscm.animals_scm
    177. for each row
    178. begin
    179.     insert into food(id, foodtype, remark, time_flag) values (1,'bamboo', 'healthy', now());
    180. end;
    181. /
    182. -- 重命名指定模式下的触发器
    183. alter trigger testscm.animals_trigger on testscm.animals_scm rename to animals_trigger_new;
    184. -- 删除指定模式下的触发器
    185. drop trigger if exists testscm.animals_trigger_new;

    相关链接

    ，DROP TRIGGER，