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

Artij

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

阅读量1.2k

点赞数 14

文章标签：数据库

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

本文链接：https://blog.csdn.net/artij/article/details/134692411

版权

修订记录

内容	时间
更正部分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,
    --TIMESTAMPTZ 表示带有时区信息的时间戳 CURRENT_TIMESTAMP PostgreSQL内置的函数,返回当前的时间戳（带时区信息）
    data JSONB -- 存储变更数据
    --JSONB  PostgreSQL 的一种二进制 JSON类型
);

重新执行触发器函数

TG_TABLE_NAME：一个触发器函数，用来获取当前被操作的表名

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 (table_name,action, data)
        VALUES (TG_TABLE_NAME,'INSERT', row_to_json(NEW)::jsonb);
        --row_to_json(NEW)::jsonb将新数据转为json格式
        RETURN NEW;--返回触发器插入的新行
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO demo_table_history (table_name,action, data)
        VALUES (TG_TABLE_NAME,'update', row_to_json(NEW)::jsonb);
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO demo_table_history (table_name,action, data)
        VALUES (TG_TABLE_NAME,'删除', row_to_json(OLD)::jsonb);
        --row_to_json(OLD)::jsonb返回旧数据
        RETURN OLD;--返回触发器删除的旧行
    END IF;--结束if语句块
END;--结束函数体
$$ LANGUAGE plpgsql;--结束函数定义，并指定语言

新建两个表进行测试

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

新增测试

正常记录对应表名

拓展2（记录修改前后的数据）

监听表新增字段: old_data; new_data;

CREATE TABLE demo_table_history (
    id SERIAL PRIMARY KEY, --id（自增）
    action VARCHAR(20), -- 记录增删改的行为
    timestamp TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    --TIMESTAMPTZ 表示带有时区信息的时间戳 CURRENT_TIMESTAMP PostgreSQL内置的函数,返回当前的时间戳（带时区信息）
    old_data JSONB, -- 存储修改前的数据
    new_data JSONB -- 存储修改后的数据
);

当触发器效果为UPDATE则执行以下sql语句

CREATE OR REPLACE FUNCTION demo_table_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO demo_table_history (action, new_data)
        VALUES ('INSERT', row_to_json(NEW)::jsonb);
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO demo_table_history (action, old_data, new_data)
        VALUES ('修改前后记录测试', row_to_json(OLD)::jsonb, row_to_json(NEW)::jsonb);
                --只修改了这一小部分
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO demo_table_history (action, old_data)
        VALUES ('DELETE', row_to_json(OLD)::jsonb);
        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;

绑个表测试

看到旧数据和修改后数据存到对应列中

拓展3（记录当前模式）

对监听表新增schema_name字段用来存储当前模式

重置：函数/触发器

CREATE OR REPLACE FUNCTION demo_table_trigger_function()
RETURNS TRIGGER AS $$
DECLARE
    current_schema TEXT;
BEGIN
    current_schema := current_schema(); -- 获取当前模式

    IF TG_OP = 'INSERT' THEN
        INSERT INTO demo_table_history (action, new_data, schema_name)
        VALUES ('INSERT', row_to_json(NEW)::jsonb, current_schema);
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO demo_table_history (action, old_data, new_data, schema_name)
        VALUES ('修改前后记录测试', row_to_json(OLD)::jsonb, row_to_json(NEW)::jsonb, current_schema);
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO demo_table_history (action, old_data, schema_name)
        VALUES ('DELETE', row_to_json(OLD)::jsonb, current_schema);
        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;

绑表测试，成功获取当前模式

拓展4（记录当前修改人账号）

监听表新增user_name字段

重置：函数/触发器

CREATE OR REPLACE FUNCTION demo_table_trigger_function()
RETURNS TRIGGER AS $$
DECLARE
    current_schema TEXT;
    current_user TEXT;
BEGIN
    current_schema := current_schema(); -- 获取当前模式
    current_user := session_user;  -- 获取当前用户

    IF TG_OP = 'INSERT' THEN
        INSERT INTO demo_table_history (action, new_data, schema_name, user_name)
        VALUES ('INSERT', row_to_json(NEW)::jsonb, current_schema, current_user);
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO demo_table_history (action, old_data, new_data, schema_name, user_name)
        VALUES ('修改前后记录测试', row_to_json(OLD)::jsonb, row_to_json(NEW)::jsonb, current_schema, current_user);
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO demo_table_history (action, old_data, schema_name, user_name)
        VALUES ('DELETE', row_to_json(OLD)::jsonb, current_schema, current_user);
        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;

成功获取到当前操作人的账号用户名

拓展5（记录当前修改ip）

新增监听表user_ip字段

重置：函数/触发器

CREATE OR REPLACE FUNCTION demo_table_trigger_function()
RETURNS TRIGGER AS $$
DECLARE
    current_schema TEXT;
    current_user TEXT;
    user_ip inet;
BEGIN
    current_schema := current_schema(); -- 获取当前模式
    current_user := session_user;  -- 获取当前用户
    user_ip := inet_client_addr(); -- 获取当前客户端的 IP 地址

    IF TG_OP = 'INSERT' THEN
        INSERT INTO demo_table_history (action, new_data, schema_name, user_name,user_ip)
        VALUES ('INSERT', row_to_json(NEW)::jsonb, current_schema, current_user, user_ip);
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO demo_table_history (action, old_data, new_data, schema_name, user_name, user_ip)
        VALUES ('修改前后记录测试', row_to_json(OLD)::jsonb, row_to_json(NEW)::jsonb, current_schema, current_user, user_ip);
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO demo_table_history (action, old_data, schema_name, user_name, user_ip)
        VALUES ('DELETE', row_to_json(OLD)::jsonb, current_schema, current_user, user_ip);
        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;

成功获取到ip

补充：

删除触发器关系：

SELECT tgname AS trigger_name,-- 触发器名称（tgname）
       tgrelid::regclass AS table_name,-- 触发器关联的表名（tgrelid::regclass）
       tgenabled AS trigger_enabled,-- 触发器是否启用（tgenabled）
       tgtype AS trigger_type,-- 触发器类型（tgtype）
       tgdeferrable AS is_deferrable,-- 触发器是否可以延迟执行（tgdeferrable）
       tginitdeferred AS init_deferred,-- 触发器是否初始延迟执行（tginitdeferred）
       proname AS function_name-- 触发器绑定的函数名称（proname）
FROM pg_trigger
JOIN pg_proc ON pg_trigger.tgfoid = pg_proc.oid;
--查看当前数据库中所有的触发器

--删除触发器
DROP TRIGGER IF EXISTS demo_table_trigger ON trigger_demo;

--例如：从 trigger_demo 表中删除 demo_table_trigger 触发器。

注意：

后面的步骤大差不差

拓展中有些步骤可能省略了，具体详细步骤参考初步实现

具体实现可按照实际业务需求做相应调整

Artij

关注

14
点赞
踩
9

收藏

觉得还不错? 一键收藏
打赏
2
评论
pgsql：对数据库的增删改查进行监听

版本：pgsql：121：新建一个日志表，用于存放对数据库的操作信息2：配置触发器，填写日志表内容。
复制链接

扫一扫