Oracle触发器代码合理性咨询：捕获字段更新前值方案

你的触发器代码存在以下几类潜在问题，部分会直接导致运行错误，部分会影响系统稳定性和可维护性：

1. 未处理异常，导致主事务回滚 #

触发器中的两个SELECT ... INTO语句未处理NO_DATA_FOUND和TOO_MANY_ROWS异常：

• 如果REF_TABLE_MAIN中不存在对应表/列的记录，或MAIN_TXN_DUMP中找不到匹配的TXN_ID，会抛出NO_DATA_FOUND，直接中断原更新操作并回滚事务。
• 如果REF_TABLE_MAIN中存在重复的表/列记录，会抛出TOO_MANY_ROWS，同样中断主事务。

示例修复：添加异常处理块

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- 可选：记录日志或忽略，避免主事务失败
        NULL;
    WHEN TOO_MANY_ROWS THEN
        RAISE_APPLICATION_ERROR(-20001, '重复的表/列参考记录');

2. 硬编码值过多，维护成本高 #

• 触发器中硬编码了表名'RT_TXN_DTL'和列名'EXPLAIN_CODE'，当表/列重命名时触发器会失效；为多个字段创建触发器时，需重复编写大量相似代码，难以维护。
• CURR_UPDATEDBY字段硬编码为'X'，无法记录实际执行更新的用户，失去审计意义。应替换为USER或SYS_CONTEXT('USERENV','SESSION_USER')获取当前会话用户。

3. 代码语法错误（疑似笔误） #

• 插入RT_LOG时使用了REF_ID而非变量v_REF_ID，会导致编译错误（REF_ID未在触发器中声明）。
• RT_LOG表的字段名为CURR_UPDATEDBY，但插入语句中写的是NEW_UPDATEDBY，同样会引发编译错误。

4. 数据类型兼容性风险 #

RT_LOG.PREV_COLUMN_VALUE定义为VARCHAR2(100)，若被审计字段是DATE、NUMBER或长度超过100的字符串：

• 会出现类型转换错误（如DATE转字符串未指定格式）；
• 长字符串会被截断，导致日志数据不完整。

建议根据实际字段类型调整日志表设计，或使用CLOB存储旧值，插入时显式转换格式（如TO_CHAR(:OLD.EXPLAIN_CODE, 'YYYY-MM-DD HH24:MI:SS')处理日期）。

5. 性能隐患 #

• 每次更新行时，触发器都会单独查询MAIN_TXN_DUMP获取TXN_REFERENCE_NUMBER，批量更新时会产生大量独立查询，严重影响性能。
• MAIN_TXN_DUMP未在TXN_ID上创建索引，查询会触发全表扫描，进一步拖慢速度。
• REF_TABLE_MAIN的查询条件是TABLE_NAME和COLUMN_NAME，但仅在REF_ID上创建了索引，建议添加复合索引加速查询：

  CREATE INDEX REF_TABLE_MAIN_TC_IX ON REF_TABLE_MAIN (TABLE_NAME, COLUMN_NAME);
  

优化建议：

• 为MAIN_TXN_DUMP.TXN_ID创建唯一索引；
• 考虑使用语句级触发器结合批量插入，减少查询次数。

6. 触发器命名与类型不符 #

触发器命名为TRG_BEFORE_UPD_RT_TXN_DTL，但实际是AFTER UPDATE触发器，命名混乱会增加后续维护难度。

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