参考Postgres XL官方文档，PostgreSQL官方文档

规则

规则（Rule）是定义在表上的，当该表发生insert、update、delete、select等操作时，自动触发其他操作。规则只能依附于某个表存在，删表时会连带删除表上的规则。当后面的被触发语句执行发生错误时，前面的触发语句也执行失败。

-- 创建表
CREATE TABLE note (
    id             serial8         PRIMARY KEY,
    content        text
);

CREATE TYPE op_type AS ENUM ('insert', 'update', 'delete', 'select');

CREATE TABLE note_oplog (
    id             serial8         PRIMARY KEY,
    op             op_type,
    old_value      text,
    new_value      text,
    op_time        timestamp       NOT NULL DEFAULT now()
);

CREATE TABLE note_oplog_2 (LIKE note_oplog INCLUDING defaults INCLUDING constraints INCLUDING indexes);

-- 在表 note 上创建规则
CREATE RULE rule_note_insert_oplog AS ON insert TO note DO ALSO insert into note_oplog (op, new_value) values ('insert', new.id||','||new.content);

CREATE OR REPLACE RULE rule_note_update_oplog AS ON update TO note DO ALSO insert into note_oplog (op, old_value, new_value) values ('update', old.id||','||old.content, new.id||','||new.content);

CREATE RULE rule_note_delete_oplog AS ON delete TO note DO ALSO insert into note_oplog (op, old_value) values ('delete', old.id||','||old.content);

CREATE RULE rule_note_oplog_insert_oplog_2 AS ON insert TO note_oplog WHERE id > 3 DO INSTEAD insert into note_oplog_2 (op, new_value) values (new.op, new.new_value);    -- 用 WHERE 限定触发条件，用 INSTEAD 表示后面的语句代替前面的触发语句，即触发语句不真正执行

\d note

INSERT INTO note (content) VALUES ('123');   -- 需要注意自增长的id，会产生跳跃

ALTER RULE rule_note_delete_oplog ON note RENAME TO rule_note_delete_oplog_xxx;

DROP RULE rule_note_delete_oplog_xxx ON note;

触发器

普通触发器（TRIGGER）是定义在表、视图和外部表上的，当该表发生insert、update、delete、truncate等操作时，自动触发一个返回值是trigger的特殊函数。普通触发器只能依附于某个表存在，删表时会连带删除表上的触发器。普通触发器创建时分为row级别和statement级别，一些内置变量如NEW、OLD只能在row级别使用。

-- 创建触发器函数
CREATE OR REPLACE FUNCTION func_note_a() RETURNS trigger AS $$
BEGIN
    IF TG_OP = 'UPDATE' THEN
        RAISE NOTICE 'Hello old: %', OLD.content;
    ELSE
        RAISE NOTICE 'Hello new: %', NEW.content;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION func_note_b() RETURNS trigger AS $$
BEGIN
    RAISE NOTICE 'Count delete in func b';
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER tg_note_before_insert 
    BEFORE insert 
    ON note
    FOR EACH ROW
    EXECUTE PROCEDURE func_note_a();

CREATE TRIGGER tg_note_after_update_or_insert 
    AFTER update OF content OR insert         -- update可以用of指定多个字段作为限制条件，逗号分隔
    ON note
    FOR EACH ROW
    WHEN (NEW.id > 3)
    EXECUTE PROCEDURE func_note_a();

CREATE TRIGGER tg_note_before_delete 
    BEFORE delete 
    ON note
    FOR EACH STATEMENT 
    EXECUTE PROCEDURE func_note_b();

\d note

INSERT INTO note (content) VALUES ('123'); 

DROP TRIGGER tg_note_before_delete ON note;

事件触发器

事件触发器（EVENT TRIGGER）是独立存在的不依附于表的全局触发器，影响范围为指定的某个库，并且可以捕获 DDL 事件。当发生ddl_command_start、ddl_command_end、table_rewrite、sql_drop等操作时，自动触发一个返回值是event_trigger的特殊函数。

-- 创建事件触发器函数
CREATE FUNCTION func_etg_drop() RETURNS event_trigger LANGUAGE plpgsql AS $$
DECLARE
    obj record;
BEGIN
    FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects()
    LOOP
        RAISE NOTICE '% dropped object: % %.% %',
                     tg_tag,
                     obj.object_type,
                     obj.schema_name,
                     obj.object_name,
                     obj.object_identity;
    END LOOP;
END
$$;

CREATE FUNCTION func_etg_when_ddl_start() RETURNS event_trigger LANGUAGE plpgsql AS $$
BEGIN
    RAISE NOTICE 'info: ddl_command_start';
END
$$;

-- 创建事件触发器
CREATE EVENT TRIGGER etg_ddl_start ON ddl_command_start
    EXECUTE PROCEDURE func_etg_when_ddl_start();

CREATE EVENT TRIGGER etg_sql_drop ON sql_drop
    EXECUTE PROCEDURE func_etg_drop();

-- 查看事件触发器
\dy

DROP TABLE note_oplog_2 CASCADE;

DROP EVENT TRIGGER etg_sql_drop;

本文基于署名-非商业性使用-相同方式共享 4.0许可协议发布，欢迎转载、使用、重新发布，但请保留文章署名wanghengbin（包含链接：https://wanghengbin.com），不得用于商业目的，基于本文修改后的作品请以相同的许可发布。