更新时间:2024-06-03 GMT+08:00

触发器函数

  • pg_get_triggerdef(oid)

    描述:获取触发器的定义信息。

    参数:待查触发器的OID。

    返回值类型:text

    示例:

    -- 创建表tri_insert。
    gaussdb=# CREATE TABLE tri_insert (a int, b int) distribute by hash(a);
    CREATE TABLE
    -- 创建函数trigger_func。
    gaussdb=# CREATE FUNCTION trigger_func() RETURNS trigger LANGUAGE plpgsql AS '
     BEGIN
     RAISE NOTICE ''trigger_func(%) called: action = %, when = %, level = %'', TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL;
     RETURN NULL;
     END;';
    CREATE FUNCTION
    -- 创建触发器before_ins_stmt_trig。
    gaussdb=# CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON tri_insert FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('before_ins_stmt');
    CREATE TRIGGER
    -- 创建触发器after_ins_when_trig。
    gaussdb=# CREATE TRIGGER after_ins_when_trig AFTER INSERT ON tri_insert FOR EACH ROW WHEN (new.a IS NOT NULL) EXECUTE PROCEDURE trigger_func('after_ins_when');
    CREATE TRIGGER
    -- 查看表tri_insert的触发器定义信息。
    gaussdb=# SELECT pg_get_triggerdef(oid) FROM pg_trigger WHERE tgrelid = 'tri_insert'::regclass;
                                                                       pg_get_triggerdef                                                                    
    --------------------------------------------------------------------------------------------------------------------------------------------------------
     CREATE TRIGGER after_ins_when_trig AFTER INSERT ON tri_insert FOR EACH ROW WHEN ((new.a IS NOT NULL)) EXECUTE PROCEDURE trigger_func('after_ins_when')
     CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON tri_insert FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('before_ins_stmt')
    (2 rows)
  • pg_get_triggerdef(oid, boolean)

    描述:获取触发器的定义信息。

    参数:待查触发器的OID及是否以pretty方式展示。

    仅在创建trigger时指定WHEN条件的情况下,布尔类型参数才生效。

    返回值类型:text

    示例:

    -- 查看表tri_insert的触发器定义信息,以非pretty形式。
    gaussdb=# SELECT pg_get_triggerdef(oid, false) FROM pg_trigger WHERE tgrelid = 'tri_insert'::regclass;
                                                                       pg_get_triggerdef                                                                    
    --------------------------------------------------------------------------------------------------------------------------------------------------------
     CREATE TRIGGER after_ins_when_trig AFTER INSERT ON tri_insert FOR EACH ROW WHEN ((new.a IS NOT NULL)) EXECUTE PROCEDURE trigger_func('after_ins_when')
     CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON tri_insert FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('before_ins_stmt')
    (2 rows)
    
    -- 查看表tri_insert的触发器定义信息,以pretty形式。
    gaussdb=# SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'tri_insert'::regclass;
                                                                      pg_get_triggerdef                                                                   
    ------------------------------------------------------------------------------------------------------------------------------------------------------
     CREATE TRIGGER after_ins_when_trig AFTER INSERT ON tri_insert FOR EACH ROW WHEN (new.a IS NOT NULL) EXECUTE PROCEDURE trigger_func('after_ins_when')
     CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON tri_insert FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('before_ins_stmt')
    (2 rows)
    
    -- 清理表tri_insert。
    gaussdb=# DROP TABLE tri_insert CASCADE;
    DROP TABLE
    -- 清理函数trigger_func。
    gaussdb=# DROP FUNCTION trigger_func;
    DROP FUNCTION