pgsql：对数据库的增删改查进行监听

Artij

已于 2024-01-07 19:23:58 修改

阅读量1.6k

点赞数 14

文章标签：数据库

于 2024-01-01 19:57:31 首次发布

本文链接：https://blog.youkuaiyun.com/Artij/article/details/134692411

版权

文章详细描述了如何在PostgreSQL12环境中实现数据库操作的实时记录，包括创建监听表、添加表名、记录修改前后数据、记录模式、用户账号和IP地址等。还涉及如何配置触发器和删除触发器关系。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

修订记录

内容	时间
更正部分sql注释内容	2024.1.4

版本：

pgsql：12

1：新建一个日志表，用于存放对数据库的操作信息

2：配置触发器，填写日志表内容

初步实现

创建一个监听表

-- 创建历史记录表
CREATE TABLE demo_table_history (
    id SERIAL PRIMARY KEY, --自增id
    action VARCHAR(10), --记录增删改的行为
    timestamp TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    --TIMESTAMPTZ 表示带有时区信息的时间戳 
    --CURRENT_TIMESTAMP PostgreSQL内置的函数,返回当前的时间戳（带时区信息）
    data JSONB -- 存储变更数据
    --JSONB  PostgreSQL 的一种二进制 JSON类型 
);

创建触发器函数

CREATE OR REPLACE FUNCTION demo_table_trigger_function()
--创建一个名为demo_table_trigger_function的函数
RETURNS TRIGGER AS $$ --$作为函数体界限的标志,可以当做代码块的{}看待
BEGIN --函数体的开始
    IF TG_OP = 'INSERT' THEN --如果触发器操作类型是INSERT 则执行以下语句块
        INSERT INTO demo_table_history (action, data)
        VALUES ('INSERT', row_to_json(NEW)::jsonb);
        --row_to_json(NEW)::jsonb将新数据转为json格式
        RETURN NEW;--返回触发器插入的新行
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO demo_table_history (action, data)
        VALUES ('update', row_to_json(NEW)::jsonb);
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO demo_table_history (action, data)
        VALUES ('删除', row_to_json(OLD)::jsonb);
        --row_to_json(OLD)::jsonb返回旧数据
        RETURN OLD;--返回触发器删除的旧行
    END IF;--结束if语句块
END;--结束函数体
$$ LANGUAGE plpgsql;--结束函数定义，并指定语言

创建触发器

CREATE TRIGGER demo_table_trigger--创建触发器的名称
AFTER INSERT OR UPDATE OR DELETE--他会在增删改操作之后触发
ON trigger_demo--绑定的表名(随意表)
FOR EACH ROW--这表示该触发器是针对每一行操作的
EXECUTE FUNCTION demo_table_trigger_function();--绑定的触发器函数

随便弄个表测试效果

CREATE TABLE trigger_demo (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    age INT,
    email VARCHAR(255),
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

用dategrip手动增删改测试，功能正常

拓展1（多表监听）

监听表新增table_name字段，用来记录被操作的表名

-- 创建历史记录表
CREATE TABLE demo_table_history (
    id SERIAL PRIMARY KEY, --自增id
    action VARCHAR(10), --记录增删改的行为
    table_name varchar(100), --记录表名
    timestamp TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    --T