如何为DB表的update/insert操作添加before/after镜像记录语句

以下两种方案可以覆盖不同场景的需求，你可以根据实际使用场景选择：

场景1：手动在SQL执行脚本中添加镜像查询（适合单批次变更脚本，不需要修改数据库配置） #

直接在你的执行脚本中，update/insert 语句前后加对应数据查询，输出内容会自动写入spool文件，以你给出的示例语句为例：

-- 开启spool输出，APPEND参数表示追加内容不会覆盖旧日志
SPOOL /your/path/operation_record.log APPEND;

-- 先记录当前要执行的操作语句
SELECT '执行操作：update employee set divison = ''IT'' where empId = 2223;' AS operation_note FROM DUAL;

-- 输出更新前镜像
SELECT '=====更新前数据=====' AS mirror_type FROM DUAL;
SELECT * FROM employee WHERE empId = 2223;

-- 执行更新操作
UPDATE employee SET divison = 'IT' WHERE empId = 2223;

-- 输出更新后镜像（提交前查询，就算后续回滚也能记录操作尝试）
SELECT '=====更新后数据（提交前）=====' AS mirror_type FROM DUAL;
SELECT * FROM employee WHERE empId = 2223;

-- 提交操作
COMMIT;
SELECT '操作已提交' AS commit_note FROM DUAL;
SPOOL OFF;

如果是insert操作，逻辑完全一致，操作前查询对应主键的行即可，返回空结果就代表插入前无对应数据。

场景2：触发器+审计表自动记录（适合全表长期审计，不需要每次改执行脚本） #

如果需要对表的所有update/insert操作都自动记录前后镜像，可以用触发器方案，以Oracle为例（MySQL语法仅需做少量适配）：

1. 先创建审计表存储镜像记录

CREATE TABLE employee_audit (
    log_id NUMBER PRIMARY KEY,
    op_type VARCHAR2(10), -- 操作类型：INSERT/UPDATE
    op_time DATE DEFAULT SYSDATE,
    op_user VARCHAR2(50) DEFAULT USER,
    -- 操作前字段
    before_empId NUMBER,
    before_name VARCHAR2(100),
    before_divison VARCHAR2(50),
    -- 操作后字段
    after_empId NUMBER,
    after_name VARCHAR2(100),
    after_divison VARCHAR2(50)
);
-- 创建自增序列用于log_id
CREATE SEQUENCE seq_emp_audit START WITH 1 INCREMENT BY 1;

2. 创建触发自动写入审计日志

CREATE OR REPLACE TRIGGER trg_emp_audit
BEFORE INSERT OR UPDATE ON employee
FOR EACH ROW
BEGIN
    IF INSERTING THEN
        INSERT INTO employee_audit(log_id, op_type, before_empId, before_name, before_divison, after_empId, after_name, after_divison)
        VALUES(seq_emp_audit.NEXTVAL, 'INSERT', NULL, NULL, NULL, :NEW.empId, :NEW.name, :NEW.divison);
    ELSIF UPDATING THEN
        INSERT INTO employee_audit(log_id, op_type, before_empId, before_name, before_divison, after_empId, after_name, after_divison)
        VALUES(seq_emp_audit.NEXTVAL, 'UPDATE', :OLD.empId, :OLD.name, :OLD.divison, :NEW.empId, :NEW.name, :NEW.divison);
    END IF;
END;
/

后续所有对employee表的变更都会自动写入审计表，需要导出到spool文件时执行对应查询即可：

SPOOL /your/path/audit_log.log;
-- 例如查询最近2小时的所有变更记录
SELECT * FROM employee_audit WHERE op_time >= SYSDATE - 1/12 ORDER BY op_time DESC;
SPOOL OFF;

内容的提问来源于stack exchange，提问作者dev123