Oracle 19c通用无硬编码列的统一日志触发器实现求助

核心思路 #

利用Oracle 19c的ANYDATA和JSON_OBJECT_T特性，实现无需硬编码列名的通用触发器：将:NEW/:OLD伪记录转换为通用数据类型后直接生成JSON，触发器逻辑完全复用，仅需修改触发器名称和目标表名；日志逻辑封装到存储过程中，后续修改只需调整存储过程。

步骤1：完善日志表结构 #

补充必要字段（如操作表名、操作用户），确保日志信息完整：

CREATE TABLE alhi_test_jn (
  jn_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  table_name VARCHAR2(128) NOT NULL,
  operation VARCHAR2(10) NOT NULL, -- 取值：INSERT/UPDATE/DELETE
  user_modified VARCHAR2(128) DEFAULT USER NOT NULL,
  date_modified DATE DEFAULT SYSDATE NOT NULL,
  old_value CLOB,
  new_value CLOB,
  CONSTRAINT chk_jn_operation CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE'))
);

步骤2：更新日志存储过程 #

新增table_name参数，专注于日志插入逻辑：

CREATE OR REPLACE PACKAGE alhi_test_pck IS
  PROCEDURE log(
    p_table_name VARCHAR2,
    p_operation VARCHAR2,
    p_old_value CLOB,
    p_new_value CLOB
  );
END alhi_test_pck;
/

CREATE OR REPLACE PACKAGE BODY alhi_test_pck IS
  PROCEDURE log(
    p_table_name VARCHAR2,
    p_operation VARCHAR2,
    p_old_value CLOB,
    p_new_value CLOB
  ) IS
    -- 如需日志独立于主事务提交，添加自治事务声明
    -- PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    INSERT INTO alhi_test_jn (
      table_name,
      operation,
      old_value,
      new_value
    ) VALUES (
      p_table_name,
      p_operation,
      p_old_value,
      p_new_value
    );
    -- 启用自治事务时需添加COMMIT;
  END log;
END alhi_test_pck;
/

步骤3：通用触发器模板 #

仅需替换触发器名称和l_table_name变量值，即可适配任意表：

CREATE OR REPLACE TRIGGER trg_[目标表名]_jn
AFTER INSERT OR UPDATE OR DELETE ON [目标表名]
FOR EACH ROW
DECLARE
  l_old_json CLOB;
  l_new_json CLOB;
  l_table_name VARCHAR2(128) := '[目标表名]'; -- 替换为大写表名
  l_operation VARCHAR2(10);

  -- 通用转换函数：ANYDATA转JSON CLOB
  FUNCTION anydata_to_json(p_data ANYDATA) RETURN CLOB IS
    l_json_obj JSON_OBJECT_T;
  BEGIN
    IF p_data IS NULL THEN
      RETURN NULL;
    END IF;
    l_json_obj := JSON_OBJECT_T(p_data);
    RETURN l_json_obj.To_Clob();
  END anydata_to_json;
BEGIN
  -- 识别操作类型并生成JSON
  IF INSERTING THEN
    l_operation := 'INSERT';
    l_new_json := anydata_to_json(ANYDATA.ConvertObject(:NEW));
  ELSIF UPDATING THEN
    l_operation := 'UPDATE';
    l_old_json := anydata_to_json(ANYDATA.ConvertObject(:OLD));
    l_new_json := anydata_to_json(ANYDATA.ConvertObject(:NEW));
  ELSIF DELETING THEN
    l_operation := 'DELETE';
    l_old_json := anydata_to_json(ANYDATA.ConvertObject(:OLD));
  END IF;

  -- 调用日志存储过程
  alhi_test_pck.log(
    p_table_name => l_table_name,
    p_operation => l_operation,
    p_old_value => l_old_json,
    p_new_value => l_new_json
  );
END trg_[目标表名]_jn;
/

方案优势 #

1. 解决伪记录访问限制：触发器内将:NEW/:OLD转换为ANYDATA通用类型，无需在存储过程或动态SQL中直接引用伪记录。
2. 无需硬编码列名：JSON_OBJECT_T自动将记录转换为包含列名和对应值的JSON，表列新增/修改时无需改动触发器。
3. 逻辑完全复用：除触发器名称和目标表名外，所有触发器代码一致，新增表时仅需复制模板修改名称。
4. 日志逻辑集中维护：后续调整日志规则（如新增字段、变更存储位置）仅需修改alhi_test_pck包体。

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