pgsql：对数据库的增删改查进行监听
目录修订记载
初步实现
拓展1（多表监听）
拓展2（记载修改前后的数据）
拓展3（记载当前模式）
拓展4（记载当前修改人账号）
拓展5（记载当前修改ip）
补充：
删除触发器关系：
注意：
修订记载
内容时间更正部分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 -- 存储变更数据
--JSONBPostgreSQL 的一种二进制 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手动增删改测试，功能正常
https://img-blog.csdnimg.cn/direct/1bce747ed5af45b386e3269c26df2796.png
拓展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 -- 存储变更数据
--JSONBPostgreSQL 的一种二进制 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
); 新增测试
正常记载对应表名
https://img-blog.csdnimg.cn/direct/6adae1e988cd4cf38eddeeade0086ed3.png
拓展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; 绑个表测试
看到旧数据和修改后数据存到对应列中
https://img-blog.csdnimg.cn/direct/f0ac0febd5884248b4bb3324d557fecf.png
拓展3（记载当前模式）
对监听表新增schema_name字段用来存储当前模式
https://img-blog.csdnimg.cn/direct/d76dbcdebf22457cae44b83af5a3b00a.png
重置：函数/触发器
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; 绑表测试，成功获取当前模式
https://img-blog.csdnimg.cn/direct/39998749541c4c29ad8a8dfd6e0396be.png
拓展4（记载当前修改人账号）
监听表新增user_name字段
https://img-blog.csdnimg.cn/direct/ffd0969c02f240f0b35fc8b58ded9540.png
重置：函数/触发器
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; 成功获取到当前利用人的账号用户名
https://img-blog.csdnimg.cn/direct/9dd1ab5dbe64455192d24e604955526d.png
拓展5（记载当前修改ip）
新增监听表user_ip字段
https://img-blog.csdnimg.cn/direct/c6215e00a04341e5925be1ccc10f9de1.png
重置：函数/触发器
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
https://img-blog.csdnimg.cn/direct/6aa276b80cf74e26838a919ba09356c9.png
补充：
删除触发器关系：
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 触发器。 注意：
后面的步骤大差不差
拓展中有些步骤可能省略了，详细详细步骤参考初步实现
详细实现可按照实际业务需求做相应调解
https://img-blog.csdnimg.cn/direct/b997f793ad1c4501943ebd65b32ac70d.png
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]