VastbaseG100

基于openGauss内核开发的企业级关系型数据库。

Menu

创建 DML 触发器

本节主要介绍创建 DML 触发器的方式。

注意事项

  • 当前仅支持在普通行存表上创建触发器,不支持在列存表、临时表、unlogged 表等类型表上创建触发器。

  • 如果为同一事件定义了多个相同类型的触发器,则按触发器的名称字母顺序触发它们。

  • 在创建 DML 触发器前,需要先创建触发器函数。

  • 触发器常用于多表间数据关联同步场景,对 SQL 执行性能影响较大,不建议在大数据量同步及对性能要求高的场景中使用。

  • 执行创建触发器操作的用户需要拥有指定表的 TRIGGER 权限或被授予了 CREATE ANY TRIGGER 权限。

  • 使用 WHEN ( condition ) 时,此处的括号不能省略,condition 需要被括号修饰。

语法格式

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

其中event包含以下几种:

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

参数说明

  • CONSTRAINT

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

  • trigger_name

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

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

  • BEFORE

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

  • AFTER

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

  • INSTEAD OF

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

  • event

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

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

    UPDATE OF column_name1 [, column_name2 ... ]
    

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

  • table_name

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

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

  • global_temporary_table

    需要创建触发器的全局临时表名。

    该功能仅在数据库兼容模式为Oracle时支持(即数据库实例初始化时指定DBCOMPATIBILITY='A'),参考《Oracle兼容性》中的SQL语法CREATE TRIGGER

  • 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

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


    关于触发器种类:

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

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

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

示例

1、创建源表及触发表。

CREATE TABLE test_trigger_src_tbl(id1 INT, id2 INT, id3 INT);
CREATE TABLE test_trigger_des_tbl(id1 INT, id2 INT, id3 INT);

2、创建触发器函数。

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;

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;

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;

3、创建INSERT触发器。

CREATE TRIGGER insert_trigger
    BEFORE INSERT ON test_trigger_src_tbl
    FOR EACH ROW
    EXECUTE PROCEDURE tri_insert_func();

4、创建UPDATE触发器。

CREATE TRIGGER update_trigger
    AFTER UPDATE ON test_trigger_src_tbl  
    FOR EACH ROW
    EXECUTE PROCEDURE tri_update_func();

5、创建DELETE触发器。

CREATE TRIGGER delete_trigger
    BEFORE DELETE ON test_trigger_src_tbl
    FOR EACH ROW
    EXECUTE PROCEDURE tri_delete_func();

6、执行INSERT触发事件并检查触发结果。

INSERT INTO test_trigger_src_tbl VALUES(100,200,300);
SELECT * FROM test_trigger_src_tbl;
SELECT * FROM test_trigger_des_tbl;  --查看触发操作是否生效。

返回结果分别为如下:

 id1 | id2 | id3
-----+-----+-----
 100 | 200 | 300
(1 row)

 id1 | id2 | id3
-----+-----+-----
 100 | 200 | 300
(1 row)

7、执行UPDATE触发事件并检查触发结果,其中第三个查询语句用于查看触发操作是否生效。

UPDATE test_trigger_src_tbl SET id3=400 WHERE id1=100;
SELECT * FROM test_trigger_src_tbl;
SELECT * FROM test_trigger_des_tbl;  

返回结果为如下:

 id1 | id2 | id3
-----+-----+-----
 100 | 200 | 400
(1 row)

 id1 | id2 | id3
-----+-----+-----
 100 | 200 | 400
(1 row)

8、执行DELETE触发事件并检查触发结果。

DELETE FROM test_trigger_src_tbl WHERE id1=100;
SELECT * FROM test_trigger_src_tbl;
SELECT * FROM test_trigger_des_tbl;  --查看触发操作是否生效

返回结果为如下:

DELETE 1

 id1 | id2 | id3
-----+-----+-----
(0 rows)

 id1 | id2 | id3
-----+-----+-----
(0 rows)

9、修改触发器。

ALTER TRIGGER delete_trigger ON test_trigger_src_tbl RENAME TO delete_trigger_renamed;

10、禁用insert_trigger触发器。

ALTER TABLE test_trigger_src_tbl DISABLE TRIGGER insert_trigger;  

11、禁用当前表上所有触发器。

ALTER TABLE test_trigger_src_tbl DISABLE TRIGGER ALL;  

12、删除触发器。

DROP TRIGGER insert_trigger ON test_trigger_src_tbl;
DROP TRIGGER update_trigger ON test_trigger_src_tbl;
DROP TRIGGER delete_trigger_renamed ON test_trigger_src_tbl;