CREATE TRIGGER

功能描述

创建一个触发器。 触发器将与指定的表或视图关联，并在特定条件下执行指定的函数。

注意事项

  • 当前仅支持在普通行存表上创建触发器，不支持在列存表、临时表、unlogged表等类型表上创建触发器。
  • 如果为同一事件定义了多个相同类型的触发器，则按触发器的名称字母顺序触发它们。
  • 触发器常用于多表间数据关联同步场景，对SQL执行性能影响较大，不建议在大数据量同步及对性能要求高的场景中使用。

语法格式

  1. CREATE [ CONSTRAINT ] TRIGGER trigger_name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
  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.     EXECUTE PROCEDURE function_name ( arguments );

其中event包含以下几种：

  1.     INSERT
  2.     UPDATE [ OF column_name [, ... ] ]
  3.     DELETE
  4.     TRUNCATE

参数说明

  • CONSTRAINT

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

  • trigger_name

    触发器名称，该名称不能限定模式，因为触发器自动继承其所在表的模式，且同一个表的触发器不能重名。 对于约束触发器，使用SET CONSTRAINTS修改触发器行为时也使用此名称。

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

  • BEFORE

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

  • AFTER

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

  • INSTEAD OF

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

  • event

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

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

    1. UPDATE OF column_name1 [, column_name2 ... ]

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

  • table_name

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

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

  • referenced_table_name

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

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

  • DEFERRABLE | NOT DEFERRABLE

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

    详细介绍请参见CREATE TABLE。

  • 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

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

    CREATE TRIGGER - 图1 说明： 关于触发器种类：

    • INSTEAD OF的触发器必须标记为FOR EACH ROW，并且只能在视图上定义。

    • BEFORE和AFTER触发器作用在视图上时，只能标记为FOR EACH STATEMENT。

    • TRUNCATE类型触发器仅限FOR EACH STATEMENT。

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

    触发时机

    触发事件

    行级

    语句级

    BEFORE

    INSERT/UPDATE/DELETE

    表

    表和视图

    TRUNCATE

    不支持

    表

    AFTER

    INSERT/UPDATE/DELETE

    表

    表和视图

    TRUNCATE

    不支持

    表

    INSTEAD OF

    INSERT/UPDATE/DELETE

    视图

    不支持

    TRUNCATE

    不支持

    不支持

    表 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. --创建INSERT触发器
  34. openGauss=# CREATE TRIGGER insert_trigger
  35.            BEFORE INSERT ON test_trigger_src_tbl
  36.            FOR EACH ROW
  37.            EXECUTE PROCEDURE tri_insert_func();
  39. --创建UPDATE触发器
  40. openGauss=# CREATE TRIGGER update_trigger
  41.            AFTER UPDATE ON test_trigger_src_tbl  
  42.            FOR EACH ROW
  43.            EXECUTE PROCEDURE tri_update_func();
  45. --创建DELETE触发器
  46. openGauss=# CREATE TRIGGER delete_trigger
  47.            BEFORE DELETE ON test_trigger_src_tbl
  48.            FOR EACH ROW
  49.            EXECUTE PROCEDURE tri_delete_func();
  51. --执行INSERT触发事件并检查触发结果
  52. openGauss=# INSERT INTO test_trigger_src_tbl VALUES(100,200,300);
  53. openGauss=# SELECT * FROM test_trigger_src_tbl;
  54. openGauss=# SELECT * FROM test_trigger_des_tbl;  //查看触发操作是否生效。
  56. --执行UPDATE触发事件并检查触发结果
  57. openGauss=# UPDATE test_trigger_src_tbl SET id3=400 WHERE id1=100;
  58. openGauss=# SELECT * FROM test_trigger_src_tbl;
  59. openGauss=# SELECT * FROM test_trigger_des_tbl;  //查看触发操作是否生效
  61. --执行DELETE触发事件并检查触发结果
  62. openGauss=# DELETE FROM test_trigger_src_tbl WHERE id1=100;
  63. openGauss=# SELECT * FROM test_trigger_src_tbl;
  64. openGauss=# SELECT * FROM test_trigger_des_tbl;  //查看触发操作是否生效
  66. --修改触发器
  67. openGauss=# ALTER TRIGGER delete_trigger ON test_trigger_src_tbl RENAME TO delete_trigger_renamed;
  69. --禁用insert_trigger触发器
  70. openGauss=# ALTER TABLE test_trigger_src_tbl DISABLE TRIGGER insert_trigger;  
  72. --禁用当前表上所有触发器
  73. openGauss=# ALTER TABLE test_trigger_src_tbl DISABLE TRIGGER ALL;  
  75. --删除触发器
  76. openGauss=# DROP TRIGGER insert_trigger ON test_trigger_src_tbl;
  77. openGauss=# DROP TRIGGER update_trigger ON test_trigger_src_tbl;
  78. openGauss=# DROP TRIGGER delete_trigger_renamed ON test_trigger_src_tbl;

相关链接

ALTER TRIGGER，DROP TRIGGER，ALTER TABLE