目录
注意:
修订记录
内容 | 时间 |
更正部分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 触发器。
注意:
后面的步骤大差不差
拓展中有些步骤可能省略了,具体详细步骤参考初步实现
具体实现可按照实际业务需求做相应调整