DB2触发器违规时记录异常的最优实现方案咨询

因为触发违规后抛出异常会导致主事务回滚，直接在触发器中插入日志表的操作也会被回滚，所以需要使用**自治事务（Autonomous Transaction）**来实现独立于主事务的日志记录。具体步骤如下：

1. 创建自治事务存储过程 #

创建带AUTONOMOUS属性的存储过程，负责插入日志。自治事务会独立提交，不受主事务回滚影响：

CREATE OR REPLACE PROCEDURE MY_SCHEMA.LOG_VIOLATION(
    IN P_OBJECT_ID INT,       -- 替换为MY_TABLE中唯一标识行的列（如ID）
    IN P_OLD_NAME VARCHAR(100), -- 可选：记录修改前的NAME值
    IN P_NEW_NAME VARCHAR(100), -- 记录触发违规的新NAME值
    IN P_ERROR_MSG VARCHAR(1000)
)
MODIFIES SQL DATA
AUTONOMOUS
BEGIN
    INSERT INTO MY_SCHEMA.LOG_TABLE(
        OBJECT_ID,
        OLD_NAME,
        NEW_NAME,
        ERROR_MESSAGE,
        LOG_TIMESTAMP
    ) VALUES (
        P_OBJECT_ID,
        P_OLD_NAME,
        P_NEW_NAME,
        P_ERROR_MSG,
        CURRENT_TIMESTAMP
    );
    COMMIT; -- 自治事务必须显式提交
END@

2. 修改触发器调用存储过程 #

在触发器中检测到违规时，先调用上述存储过程记录日志，再抛出异常：

CREATE OR REPLACE TRIGGER XYZ 
    NO CASCADE BEFORE UPDATE OF NAME ON MY_TABLE
    REFERENCING OLD AS OLD_OBJ NEW AS OBJ
    FOR EACH ROW MODE DB2SQL 
BEGIN
    DECLARE ERROR_TEXT VARCHAR(1000);
    IF OBJ.NAME = 'Z' THEN
        -- 调用自治事务存储过程写入日志
        CALL MY_SCHEMA.LOG_VIOLATION(OLD_OBJ.ID, OLD_OBJ.NAME, OBJ.NAME, 'Name not allowed');
        SET ERROR_TEXT = 'Name not allowed';
        SIGNAL SQLSTATE '7010101' SET MESSAGE_TEXT = ERROR_TEXT;
    END IF;
END@

关键说明 #

• 自治事务的核心是AUTONOMOUS关键字，它让存储过程的操作在独立事务中执行，提交后不会被主事务的回滚撤销。
• 存储过程内必须显式执行COMMIT，否则日志插入不会生效。
• 可根据实际需求调整存储过程的参数和日志表的字段，比如添加操作用户、客户端IP等信息。

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