创建带条件更新的Insert触发器：保障分步执行与数据不回滚

Alright, let's break down how to build this trigger exactly as you specified. We'll use an AFTER INSERT trigger (since we need to act on the rows already inserted into t via the inserted virtual table) and leverage SQL Server's TRY...CATCH blocks to handle the failure isolation requirement.

Key Requirements Recap & Implementation Logic #

• Step 1: Always update dbupddate for the inserted rows, regardless of subsequent success/failure. We'll place this outside any error-handling blocks to ensure it never gets rolled back.
• Step 2: Insert the new rows into someothertable. If this fails, we don't want to undo Step 1.
• Step 3: Only update transfer_date if Step 2 succeeds. Failed Step 2 attempts will leave transfer_date as NULL, which you can use to troubleshoot failures later.

Full Trigger Code #

CREATE TRIGGER trg_t_AfterInsert
ON t
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON; -- Suppress unnecessary row count messages

    -- Step 1: Always execute this update, no rollback even if later steps fail
    UPDATE t
    SET dbupddate = GETDATE()
    WHERE t.code IN (SELECT code FROM inserted);

    -- Handle Steps 2 and 3 with error isolation
    BEGIN TRY
        -- Step 2: Insert the new rows into someothertable
        INSERT INTO someothertable
        SELECT * FROM inserted; -- Note: Ensure column structures match! If not, explicitly list columns.

        -- Step 3: Only run this if Step 2 succeeded
        UPDATE t
        SET transfer_date = GETDATE()
        WHERE t.code IN (SELECT code FROM inserted);
    END TRY
    BEGIN CATCH
        -- No rollback needed here: Step 1 is already committed, and we just skip Step 3
        -- Optional: Add error logging here (e.g., insert into an error log table with ERROR_MESSAGE(), ERROR_NUMBER())
        -- to get more details about why Step 2 failed
    END CATCH
END
GO

Important Notes #

• Trigger Type: We use AFTER INSERT instead of INSTEAD OF INSERT because we need the rows to be fully inserted into t first before running our follow-up logic.
• Column Matching: The SELECT * in Step 2 assumes someothertable has the exact same column structure (order, data types, nullability) as t. If that's not the case, replace SELECT * with an explicit list of matching columns (e.g., SELECT code, col2, col3 FROM inserted).
• Troubleshooting: As requested, any rows where transfer_date is NULL indicate that Step 2 failed for that insert. You can query these rows with:

  SELECT * FROM t WHERE transfer_date IS NULL;
  

• Error Logging (Optional): If you want more context on failures, add code in the CATCH block to log errors. For example:

  INSERT INTO ErrorLog (ErrorTime, ErrorNumber, ErrorMessage)
  VALUES (GETDATE(), ERROR_NUMBER(), ERROR_MESSAGE());
  

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