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

南飓风  金牌会员 | 2024-6-15 01:47:00 | 显示全部楼层 | 阅读模式
打印 上一主题 下一主题
楼主

主题 820|帖子 820|积分 2460

目录
修订记载
初步实现
拓展1（多表监听）
拓展2（记载修改前后的数据）
拓展3（记载当前模式）
拓展4（记载当前修改人账号）
拓展5（记载当前修改ip）
补充：
     删除触发器关系：
注意：​​​​​​​
修订记载

内容时间
更正部分sql解释内容2024.1.4
版本：
pgsql：12
1：新建一个日志表，用于存放对数据库的利用信息
2：设置触发器，填写日志表内容
初步实现

创建一个监听表
  1. -- 创建历史记录表
  2. CREATE TABLE demo_table_history (
  3.     id SERIAL PRIMARY KEY, --自增id
  4.     action VARCHAR(10), --记录增删改的行为
  5.     timestamp TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  6.     --TIMESTAMPTZ 表示带有时区信息的时间戳
  7.     --CURRENT_TIMESTAMP PostgreSQL内置的函数,返回当前的时间戳（带时区信息）
  8.     data JSONB -- 存储变更数据
  9.     --JSONB  PostgreSQL 的一种二进制 JSON类型
  10. );
复制代码
创建触发器函数
  1. CREATE OR REPLACE FUNCTION demo_table_trigger_function()
  2. --创建一个名为demo_table_trigger_function的函数
  3. RETURNS TRIGGER AS $$ --$作为函数体界限的标志,可以当做代码块的{}看待
  4. BEGIN --函数体的开始
  5.     IF TG_OP = 'INSERT' THEN --如果触发器操作类型是INSERT 则执行以下语句块
  6.         INSERT INTO demo_table_history (action, data)
  7.         VALUES ('INSERT', row_to_json(NEW)::jsonb);
  8.         --row_to_json(NEW)::jsonb将新数据转为json格式
  9.         RETURN NEW;--返回触发器插入的新行
  10.     ELSIF TG_OP = 'UPDATE' THEN
  11.         INSERT INTO demo_table_history (action, data)
  12.         VALUES ('update', row_to_json(NEW)::jsonb);
  13.         RETURN NEW;
  14.     ELSIF TG_OP = 'DELETE' THEN
  15.         INSERT INTO demo_table_history (action, data)
  16.         VALUES ('删除', row_to_json(OLD)::jsonb);
  17.         --row_to_json(OLD)::jsonb返回旧数据
  18.         RETURN OLD;--返回触发器删除的旧行
  19.     END IF;--结束if语句块
  20. END;--结束函数体
  21. $$ LANGUAGE plpgsql;--结束函数定义，并指定语言
复制代码
创建触发器
  1. CREATE TRIGGER demo_table_trigger--创建触发器的名称
  2. AFTER INSERT OR UPDATE OR DELETE--他会在增删改操作之后触发
  3. ON trigger_demo--绑定的表名(随意表)
  4. FOR EACH ROW--这表示该触发器是针对每一行操作的
  5. EXECUTE FUNCTION demo_table_trigger_function();--绑定的触发器函数
复制代码
恣意弄个表测试结果
  1. CREATE TABLE trigger_demo (
  2.     id SERIAL PRIMARY KEY,
  3.     name VARCHAR(255) NOT NULL,
  4.     age INT,
  5.     email VARCHAR(255),
  6.     created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
  7. );
复制代码
用dategrip手动增删改测试，功能正常

拓展1（多表监听）

监听表新增table_name字段，用来记载被利用的表名
  1. -- 创建历史记录表
  2. CREATE TABLE demo_table_history (
  3.     id SERIAL PRIMARY KEY, --自增id
  4.     action VARCHAR(10), --记录增删改的行为
  5.     table_name varchar(100), --记录表名
  6.     timestamp TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  7.     --TIMESTAMPTZ 表示带有时区信息的时间戳 CURRENT_TIMESTAMP PostgreSQL内置的函数,返回当前的时间戳（带时区信息）
  8.     data JSONB -- 存储变更数据
  9.     --JSONB  PostgreSQL 的一种二进制 JSON类型
  10. );
复制代码
重新执行触发器函数
TG_TABLE_NAME：一个触发器函数，用来获取当前被利用的表名
  1. CREATE OR REPLACE FUNCTION demo_table_trigger_function()
  2. --创建一个名为demo_table_trigger_function的函数
  3. RETURNS TRIGGER AS $$ --$作为函数体界限的标志,可以当做代码块的{}看待
  4. BEGIN --函数体的开始
  5.     IF TG_OP = 'INSERT' THEN --如果触发器操作类型是INSERT 则执行以下语句块
  6.         INSERT INTO demo_table_history (table_name,action, data)
  7.         VALUES (TG_TABLE_NAME,'INSERT', row_to_json(NEW)::jsonb);
  8.         --row_to_json(NEW)::jsonb将新数据转为json格式
  9.         RETURN NEW;--返回触发器插入的新行
  10.     ELSIF TG_OP = 'UPDATE' THEN
  11.         INSERT INTO demo_table_history (table_name,action, data)
  12.         VALUES (TG_TABLE_NAME,'update', row_to_json(NEW)::jsonb);
  13.         RETURN NEW;
  14.     ELSIF TG_OP = 'DELETE' THEN
  15.         INSERT INTO demo_table_history (table_name,action, data)
  16.         VALUES (TG_TABLE_NAME,'删除', row_to_json(OLD)::jsonb);
  17.         --row_to_json(OLD)::jsonb返回旧数据
  18.         RETURN OLD;--返回触发器删除的旧行
  19.     END IF;--结束if语句块
  20. END;--结束函数体
  21. $$ LANGUAGE plpgsql;--结束函数定义，并指定语言
复制代码
新建两个表进行测试
  1. CREATE TABLE trigger_demo1 (
  2.     id SERIAL PRIMARY KEY,
  3.     name VARCHAR(255) NOT NULL,
  4.     age INT,
  5.     email VARCHAR(255),
  6.     created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
  7. );
  8. CREATE TABLE trigger_demo2 (
  9.     id SERIAL PRIMARY KEY,
  10.     name VARCHAR(255) NOT NULL,
  11.     age INT,
  12.     email VARCHAR(255),
  13.     created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
  14. );
复制代码
新增测试
正常记载对应表名

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

监听表新增字段:   old_data;  new_data;
  1. CREATE TABLE demo_table_history (
  2.     id SERIAL PRIMARY KEY, --id（自增）
  3.     action VARCHAR(20), -- 记录增删改的行为
  4.     timestamp TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  5.     --TIMESTAMPTZ 表示带有时区信息的时间戳 CURRENT_TIMESTAMP PostgreSQL内置的函数,返回当前的时间戳（带时区信息）
  6.     old_data JSONB, -- 存储修改前的数据
  7.     new_data JSONB -- 存储修改后的数据
  8. );
复制代码
 当触发器结果为UPDATE则执行以下sql语句
  1. CREATE OR REPLACE FUNCTION demo_table_trigger()
  2. RETURNS TRIGGER AS $$
  3. BEGIN
  4.     IF TG_OP = 'INSERT' THEN
  5.         INSERT INTO demo_table_history (action, new_data)
  6.         VALUES ('INSERT', row_to_json(NEW)::jsonb);
  7.         RETURN NEW;
  8.     ELSIF TG_OP = 'UPDATE' THEN
  9.         INSERT INTO demo_table_history (action, old_data, new_data)
  10.         VALUES ('修改前后记录测试', row_to_json(OLD)::jsonb, row_to_json(NEW)::jsonb);
  11.                 --只修改了这一小部分
  12.         RETURN NEW;
  13.     ELSIF TG_OP = 'DELETE' THEN
  14.         INSERT INTO demo_table_history (action, old_data)
  15.         VALUES ('DELETE', row_to_json(OLD)::jsonb);
  16.         RETURN OLD;
  17.     END IF;
  18. END;
  19. $$ LANGUAGE plpgsql;
复制代码
 绑个表测试
看到旧数据和修改后数据存到对应列中

拓展3（记载当前模式）

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

重置：函数/触发器
  1. CREATE OR REPLACE FUNCTION demo_table_trigger_function()
  2. RETURNS TRIGGER AS $$
  3. DECLARE
  4.     current_schema TEXT;
  5. BEGIN
  6.     current_schema := current_schema(); -- 获取当前模式
  8.     IF TG_OP = 'INSERT' THEN
  9.         INSERT INTO demo_table_history (action, new_data, schema_name)
  10.         VALUES ('INSERT', row_to_json(NEW)::jsonb, current_schema);
  11.         RETURN NEW;
  12.     ELSIF TG_OP = 'UPDATE' THEN
  13.         INSERT INTO demo_table_history (action, old_data, new_data, schema_name)
  14.         VALUES ('修改前后记录测试', row_to_json(OLD)::jsonb, row_to_json(NEW)::jsonb, current_schema);
  15.         RETURN NEW;
  16.     ELSIF TG_OP = 'DELETE' THEN
  17.         INSERT INTO demo_table_history (action, old_data, schema_name)
  18.         VALUES ('DELETE', row_to_json(OLD)::jsonb, current_schema);
  19.         RETURN OLD;
  20.     END IF;
  21. END;
  22. $$ LANGUAGE plpgsql;
复制代码
绑表测试，成功获取当前模式 

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

监听表新增user_name字段

重置：函数/触发器
  1. CREATE OR REPLACE FUNCTION demo_table_trigger_function()
  2. RETURNS TRIGGER AS $$
  3. DECLARE
  4.     current_schema TEXT;
  5.     current_user TEXT;
  6. BEGIN
  7.     current_schema := current_schema(); -- 获取当前模式
  8.     current_user := session_user;  -- 获取当前用户
  10.     IF TG_OP = 'INSERT' THEN
  11.         INSERT INTO demo_table_history (action, new_data, schema_name, user_name)
  12.         VALUES ('INSERT', row_to_json(NEW)::jsonb, current_schema, current_user);
  13.         RETURN NEW;
  14.     ELSIF TG_OP = 'UPDATE' THEN
  15.         INSERT INTO demo_table_history (action, old_data, new_data, schema_name, user_name)
  16.         VALUES ('修改前后记录测试', row_to_json(OLD)::jsonb, row_to_json(NEW)::jsonb, current_schema, current_user);
  17.         RETURN NEW;
  18.     ELSIF TG_OP = 'DELETE' THEN
  19.         INSERT INTO demo_table_history (action, old_data, schema_name, user_name)
  20.         VALUES ('DELETE', row_to_json(OLD)::jsonb, current_schema, current_user);
  21.         RETURN OLD;
  22.     END IF;
  23. END;
  24. $$ LANGUAGE plpgsql;
复制代码
成功获取到当前利用人的账号用户名

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

新增监听表user_ip字段

重置：函数/触发器
  1. CREATE OR REPLACE FUNCTION demo_table_trigger_function()
  2. RETURNS TRIGGER AS $$
  3. DECLARE
  4.     current_schema TEXT;
  5.     current_user TEXT;
  6.     user_ip inet;
  7. BEGIN
  8.     current_schema := current_schema(); -- 获取当前模式
  9.     current_user := session_user;  -- 获取当前用户
  10.     user_ip := inet_client_addr(); -- 获取当前客户端的 IP 地址
  12.     IF TG_OP = 'INSERT' THEN
  13.         INSERT INTO demo_table_history (action, new_data, schema_name, user_name,user_ip)
  14.         VALUES ('INSERT', row_to_json(NEW)::jsonb, current_schema, current_user, user_ip);
  15.         RETURN NEW;
  16.     ELSIF TG_OP = 'UPDATE' THEN
  17.         INSERT INTO demo_table_history (action, old_data, new_data, schema_name, user_name, user_ip)
  18.         VALUES ('修改前后记录测试', row_to_json(OLD)::jsonb, row_to_json(NEW)::jsonb, current_schema, current_user, user_ip);
  19.         RETURN NEW;
  20.     ELSIF TG_OP = 'DELETE' THEN
  21.         INSERT INTO demo_table_history (action, old_data, schema_name, user_name, user_ip)
  22.         VALUES ('DELETE', row_to_json(OLD)::jsonb, current_schema, current_user, user_ip);
  23.         RETURN OLD;
  24.     END IF;
  25. END;
  26. $$ LANGUAGE plpgsql;
复制代码
成功获取到ip

补充：

     删除触发器关系：

  1. SELECT tgname AS trigger_name,-- 触发器名称（tgname）
  2.        tgrelid::regclass AS table_name,-- 触发器关联的表名（tgrelid::regclass）
  3.        tgenabled AS trigger_enabled,-- 触发器是否启用（tgenabled）
  4.        tgtype AS trigger_type,-- 触发器类型（tgtype）
  5.        tgdeferrable AS is_deferrable,-- 触发器是否可以延迟执行（tgdeferrable）
  6.        tginitdeferred AS init_deferred,-- 触发器是否初始延迟执行（tginitdeferred）
  7.        proname AS function_name-- 触发器绑定的函数名称（proname）
  8. FROM pg_trigger
  9. JOIN pg_proc ON pg_trigger.tgfoid = pg_proc.oid;
  10. --查看当前数据库中所有的触发器
复制代码
  1. --删除触发器
  2. DROP TRIGGER IF EXISTS demo_table_trigger ON trigger_demo;
  4. --例如：从 trigger_demo 表中删除 demo_table_trigger 触发器。
复制代码
注意：

后面的步骤大差不差
        拓展中有些步骤可能省略了，详细详细步骤参考初步实现
详细实现可按照实际业务需求做相应调解


免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
回复

使用道具 举报

0 个回复

倒序浏览
返回列表

快速回复

高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 or 立即注册

本版积分规则

南飓风

金牌会员
这个人很懒什么都没写!

楼主热帖

标签云

挺好的 服务器
微信订阅号
微信服务号
微信客服
小程序
H5
关于我们 商务合作 网站地图
©Copyright 2022-2024 杭州祥升科技有限公司 版权所有 浙ICP备20004199号
快速回复 返回顶部 返回列表