参考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),不得用于商业目的,基于本文修改后的作品请以相同的许可发布。