sql语句怎样避免因触发器中sql语句错误导致的主操作失败 sql语句触发器中错误致主操作失败的常见问题解决

TRY...CATCH

EXCEPTION

TRY...CATCH

TRY...CATCH

CREATE TRIGGER trg_YourTable_AfterInsertUpdate
ON YourTable
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON; -- 防止触发器对客户端发送多余的行数信息

    BEGIN TRY
        -- ---------------------------------------------------
        -- 触发器核心业务逻辑
        -- ---------------------------------------------------
        -- 示例1: 模拟一个数据类型转换错误
        -- DECLARE @invalid_num INT = CAST('abc' AS INT);

        -- 示例2: 模拟一个除以零错误
        -- DECLARE @result DECIMAL(10,2);
        -- SET @result = 100 / 0;

        -- 示例3: 正常业务逻辑，比如更新关联表或记录审计信息
        INSERT INTO AuditLog (TableName, OperationType, ChangeDate, ChangedBy, OldValue, NewValue)
        SELECT
            'YourTable',
            CASE WHEN EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) THEN 'UPDATE'
                 WHEN EXISTS (SELECT * FROM inserted) THEN 'INSERT'
                 WHEN EXISTS (SELECT * FROM deleted) THEN 'DELETE'
            END,
            GETDATE(),
            SUSER_SNAME(),
            (SELECT OldCol FROM deleted), -- 假设OldCol是你要记录的旧值
            (SELECT NewCol FROM inserted); -- 假设NewCol是你要记录的新值

        -- 注意：在SQL Server的AFTER触发器中，inserted和deleted表可能包含多行
        -- 上述单行SELECT示例在多行操作时会报错，实际应使用JOIN或循环处理
        -- 正确的多行处理示例：
        -- INSERT INTO AuditLog (TableName, OperationType, ChangeDate, ChangedBy, KeyValue, OldValue, NewValue)
        -- SELECT
        --     'YourTable',
        --     'UPDATE',
        --     GETDATE(),
        --     SUSER_SNAME(),
        --     i.ID, -- 假设ID是主键
        --     d.SomeColumn,
        --     i.SomeColumn
        -- FROM inserted i
        -- JOIN deleted d ON i.ID = d.ID;

    END TRY
    BEGIN CATCH
        -- ---------------------------------------------------
        -- 错误处理和日志记录部分
        -- ---------------------------------------------------
        INSERT INTO TriggerErrorLog (
            ErrorNumber,
            ErrorSeverity,
            ErrorState,
            ErrorProcedure,
            ErrorLine,
            ErrorMessage,
            TriggerName,
            ErrorTime,
            AffectedRowsJson -- 可以存储inserted/deleted表的JSON表示
        )
        VALUES (
            ERROR_NUMBER(),
            ERROR_SEVERITY(),
            ERROR_STATE(),
            ERROR_PROCEDURE(),
            ERROR_LINE(),
            ERROR_MESSAGE(),
            OBJECT_NAME(@@PROCID), -- 获取当前触发器的名称
            GETDATE(),
            (SELECT (SELECT * FROM inserted FOR JSON AUTO) AS inserted_data,
                    (SELECT * FROM deleted FOR JSON AUTO) AS deleted_data FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)
        );

        -- 如果你想让主操作继续成功，则不要执行RAISERROR
        -- 如果你希望主操作失败并回滚，则可以重新抛出错误：
        -- RAISERROR ('触发器执行失败，详细信息已记录。', 16, 1);
        -- 16是严重级别，1是状态。严重级别16表示一般错误，会终止事务。
        -- 也可以使用THROW，THROW会保留原始错误信息：
        -- THROW;

    END CATCH
END;
GO

-- 创建错误日志表 (如果不存在)
CREATE TABLE TriggerErrorLog (
    LogID INT IDENTITY(1,1) PRIMARY KEY,
    ErrorNumber INT,
    ErrorSeverity INT,
    ErrorState INT,
    ErrorProcedure NVARCHAR(128),
    ErrorLine INT,
    ErrorMessage NVARCHAR(MAX),
    TriggerName NVARCHAR(128),
    ErrorTime DATETIME DEFAULT GETDATE(),
    AffectedRowsJson NVARCHAR(MAX) -- 存储受影响行的JSON数据
);
GO

EXCEPTION

EXCEPTION

CREATE OR REPLACE TRIGGER trg_YourTable_AfterInsertUpdate
AFTER INSERT OR UPDATE ON YourTable
FOR EACH ROW -- 行级触发器，对每一行操作执行一次
DECLARE
    -- 声明变量
    v_error_message VARCHAR2(4000);
    PRAGMA AUTONOMOUS_TRANSACTION; -- 用于日志记录，使其不影响主事务的提交/回滚
BEGIN
    -- ---------------------------------------------------
    -- 触发器核心业务逻辑
    -- ---------------------------------------------------
    -- 示例1: 模拟一个数据类型转换错误
    -- DECLARE v_num NUMBER;
    -- v_num := 'abc';

    -- 示例2: 模拟一个除以零错误
    -- DECLARE v_result NUMBER;
    -- v_result := 100 / 0;

    -- 示例3: 正常业务逻辑，比如记录审计信息
    INSERT INTO AuditLog (table_name, operation_type, change_date, changed_by, old_value, new_value)
    VALUES (
        'YourTable',
        CASE WHEN INSERTING THEN 'INSERT' WHEN UPDATING THEN 'UPDATE' ELSE 'DELETE' END,
        SYSDATE,
        USER,
        :OLD.some_column, -- 访问旧值
        :NEW.some_column   -- 访问新值
    );

EXCEPTION
    WHEN OTHERS THEN -- 捕获所有其他未预期的异常
        -- ---------------------------------------------------
        -- 错误处理和日志记录部分
        -- ---------------------------------------------------
        v_error_message := SQLERRM; -- 获取错误消息

        -- 将错误记录到日志表
        INSERT INTO TriggerErrorLog (
            error_code,
            error_message,
            trigger_name,
            error_time,
            affected_old_row_json, -- 存储旧行数据
            affected_new_row_json  -- 存储新行数据
        )
        VALUES (
            SQLCODE, -- 获取错误代码
            v_error_message,
            'TRG_YOURTABLE_AFTERINSERTUPDATE', -- 触发器名称
            SYSDATE,
            -- 可以将 :OLD 和 :NEW 行转换为JSON或XML存储，取决于你的需求
            -- 例如：UTL_JSON.to_json_string(:OLD) 或自定义函数
            NULL, -- 占位符
            NULL  -- 占位符
        );
        COMMIT; -- 提交错误日志的事务，因为使用了AUTONOMOUS_TRANSACTION

        -- 如果你想让主操作继续成功，则不要执行 RAISE;
        -- 如果你希望主操作失败并回滚，则可以重新抛出错误：
        -- RAISE;
END;
/

-- 创建错误日志表 (如果不存在)
CREATE TABLE TriggerErrorLog (
    log_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
    error_code NUMBER,
    error_message VARCHAR2(4000),
    trigger_name VARCHAR2(128),
    error_time DATE DEFAULT SYSDATE,
    affected_old_row_json CLOB,
    affected_new_row_json CLOB
);
/