PL/SQL语句触发器如何实现类似行触发器:old/:new的审计记录功能

实现方案说明 #

首先确认：单独的语句触发器本身确实无法直接获取每行被修改记录的:old/:new变量，你之前的认知是正确的。老师要求的实现方式需要搭配临时表做数据中转，用「行级触发器暂存修改行数据+语句级触发器批量写入审计表」的组合方案完成，具体实现步骤如下：

步骤1：创建所需表结构 #

首先创建审计表（补充老师要求的操作类型、时间戳字段）和临时中转表：

-- 1. 审计表，存储最终的审计数据
CREATE TABLE audit_trade (
    client_id NUMBER,
    client_name VARCHAR2(100),
    client_budget NUMBER,
    op_type VARCHAR2(10), -- 记录操作类型：UPDATE/DELETE
    op_time DATE -- 记录操作时间
);

-- 2. 全局临时表，暂存单次DML操作影响的所有行的旧值，事务提交后自动清空
CREATE GLOBAL TEMPORARY TABLE temp_client_modify (
    client_id NUMBER,
    client_name VARCHAR2(100),
    client_budget NUMBER
) ON COMMIT DELETE ROWS;

步骤2：创建行级前置触发器做数据暂存 #

这个触发器只负责把每次修改的行的旧值存入临时表，不直接写审计表：

CREATE OR REPLACE TRIGGER trg_row_before_modify
BEFORE UPDATE OR DELETE ON client_master
FOR EACH ROW
BEGIN
    INSERT INTO temp_client_modify VALUES (:old.client_id, :old.client_name, :old.client_budget);
END;
/

步骤3：创建语句级触发器完成审计写入 #

这就是作业要求的语句触发器，在整个DML语句执行完成后触发，批量把临时表中暂存的所有被修改行数据写入审计表：

CREATE OR REPLACE TRIGGER trg_stmt_after_modify
AFTER UPDATE OR DELETE ON client_master
-- 没有FOR EACH ROW关键字，默认就是语句级触发器
DECLARE
    v_op_type VARCHAR2(10);
BEGIN
    -- 判断当前操作类型
    IF UPDATING THEN
        v_op_type := 'UPDATE';
    ELSIF DELETING THEN
        v_op_type := 'DELETE';
    END IF;

    -- 批量写入审计表
    INSERT INTO audit_trade(client_id, client_name, client_budget, op_type, op_time)
    SELECT client_id, client_name, client_budget, v_op_type, SYSDATE
    FROM temp_client_modify;

    -- 清空临时表避免数据残留
    DELETE FROM temp_client_modify;
END;
/

方案验证 #

执行任意UPDATE或DELETE语句修改client_master表的数据后，查询audit_trade表就可以看到所有被修改行的旧值、操作类型和操作时间，和你之前行触发器实现的效果完全一致。

如果你使用的是Oracle 11g及以上版本，还可以把上述两个触发器合并为一个复合触发器，代码结构会更简洁。

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