如何正确改写现有更新触发器以处理多行更新？

Let's start with the root cause of your problem: your current trigger uses scalar variables that only capture a single row from INSERTED (via SELECT TOP 1), so when you run a multi-row update, all but one of your changes get completely ignored. To fix this, we need to handle sets of rows instead of individual values, and process each affected row properly—using a lightweight loop (since we have to call an external API per row) instead of a cursor.

Rewritten Trigger Code #

BEGIN
    SET NOCOUNT ON;

    -- Create a temp table to track all rows needing geocoding
    CREATE TABLE #GeocodeQueue (
        Mailing_Address_ID INT,
        Entity_Class_ID INT,
        Entity_Identifier VARCHAR(50),
        Postal_Code VARCHAR(100),
        Processed BIT DEFAULT 0
    );

    -- Populate the queue with rows where Postal_Code changed AND Entity_Class_ID = 3
    INSERT INTO #GeocodeQueue (Mailing_Address_ID, Entity_Class_ID, Entity_Identifier, Postal_Code)
    SELECT 
        i.Mailing_Address_ID,
        i.Entity_Class_ID,
        i.Entity_Identifier,
        i.Postal_Code
    FROM INSERTED i
    JOIN DELETED d ON i.Mailing_Address_ID = d.Mailing_Address_ID
    WHERE i.Postal_Code <> d.Postal_Code
        AND i.Entity_Class_ID = 3
        AND i.Postal_Code IS NOT NULL;

    -- Process each row in the queue one by one
    DECLARE 
        @CurrentID INT,
        @Entity_Identifier VARCHAR(50),
        @Postcode VARCHAR(100),
        @Latitude NUMERIC(18,9),
        @Longitude NUMERIC(18,9);

    WHILE EXISTS (SELECT 1 FROM #GeocodeQueue WHERE Processed = 0)
    BEGIN
        -- Grab the next unprocessed row
        SELECT TOP 1
            @CurrentID = Mailing_Address_ID,
            @Entity_Identifier = Entity_Identifier,
            @Postcode = Postal_Code
        FROM #GeocodeQueue
        WHERE Processed = 0
        ORDER BY Mailing_Address_ID;

        -- Call Bing Maps API to retrieve geocode data
        DECLARE 
            @URL VARCHAR(MAX),
            @Response VARCHAR(8000),
            @XML XML,
            @obj INT,
            @Result INT,
            @HTTPStatus INT,
            @ErrorMsg VARCHAR(MAX);

        SET @URL = 'http://dev.virtualearth.net/REST/v1/Locations/UK/' + @Postcode + '?o=xml&key=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';

        BEGIN TRY
            EXEC @Result = sp_OACreate 'MSXML2.ServerXMLHttp', @Obj OUT;
            EXEC @Result = sp_OAMethod @Obj, 'open', NULL, 'GET', @URL, false;
            EXEC @Result = sp_OAMethod @Obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded';
            EXEC @Result = sp_OAMethod @obj, send, NULL, '';
            EXEC @Result = sp_OAGetProperty @Obj, 'status', @HTTPStatus OUT;
            EXEC @Result = sp_OAGetProperty @Obj, 'responseXML.xml', @Response OUT;
        END TRY
        BEGIN CATCH
            SET @ErrorMsg = ERROR_MESSAGE();
        END CATCH

        EXEC @Result = sp_OADestroy @Obj;

        -- Handle API errors for this specific row
        IF (@ErrorMsg IS NOT NULL) OR (@HTTPStatus <> 200)
        BEGIN
            SET @ErrorMsg = 'Error geocoding Entity_Identifier ' + @Entity_Identifier + ': ' + ISNULL(@ErrorMsg, 'HTTP status: ' + CAST(@HTTPStatus AS VARCHAR(10)));
            RAISERROR(@ErrorMsg, 16, 1);
            -- Mark as processed to avoid infinite loop (remove if you want to retry later)
            UPDATE #GeocodeQueue SET Processed = 1 WHERE Mailing_Address_ID = @CurrentID;
            CONTINUE;
        END

        -- Parse XML response to extract latitude/longitude
        SET @XML = CAST(@Response AS XML);
        SET @Latitude = @XML.value(' 
            declare namespace BM="http://schemas.microsoft.com/search/local/ws/rest/v1";
            (/BM:Response/BM:ResourceSets/BM:ResourceSet/BM:Resources/BM:Location/BM:Point/BM:Latitude)[1]', 'numeric(18,9)');
        SET @Longitude = @XML.value(' 
            declare namespace BM="http://schemas.microsoft.com/search/local/ws/rest/v1";
            (/BM:Response/BM:ResourceSets/BM:ResourceSet/BM:Resources/BM:Location/BM:Point/BM:Longitude)[1]', 'numeric(18,9)');

        -- Upsert to EXT_BI_Additional_Fields (replace separate IF EXISTS check)
        MERGE INTO EXT_BI_Additional_Fields target
        USING (
            SELECT 
                p.Project_ID,
                @Latitude AS Latitude,
                @Longitude AS Longitude,
                p.Project_Number,
                p.Project_Code
            FROM dbo.Project p
            WHERE p.Project_ID = @Entity_Identifier
        ) source
        ON target.Project_ID = source.Project_ID
        WHEN MATCHED THEN
            UPDATE SET 
                target.Latitude = source.Latitude,
                target.Longitude = source.Longitude
        WHEN NOT MATCHED THEN
            INSERT (Project_ID, Latitude, Longitude, Project_Number, Project_Code)
            VALUES (source.Project_ID, source.Latitude, source.Longitude, source.Project_Number, source.Project_Code);

        -- Mark row as processed
        UPDATE #GeocodeQueue SET Processed = 1 WHERE Mailing_Address_ID = @CurrentID;
    END

    -- Clean up temporary table
    DROP TABLE #GeocodeQueue;
END

Key Improvements Explained #

1. Batch-First Processing: We first collect all eligible rows (Postal_Code changed, Entity_Class_ID=3, non-null Postcode) into a temp table, ensuring no rows are missed during multi-row updates.
2. Lightweight WHILE Loop: Instead of a cursor, we use a WHILE loop to process rows individually. This is necessary because we can't make external API calls in a set-based operation.
3. MERGE Statement: Replaced the clunky IF EXISTS + UPDATE/INSERT with a MERGE—the standard, efficient way to handle upserts in SQL Server.
4. Per-Row Error Handling: Errors are captured for each row, and we mark failed rows as processed to avoid infinite loops. Adjust this if you want to retry failed geocodes later.
5. Explicit Joins: We join INSERTED and DELETED on Mailing_Address_ID (assuming it's the primary key) to accurately identify rows where Postal_Code changed, instead of relying on scalar checks.

Critical Best Practices to Adopt #

• Avoid External API Calls in Triggers: Calling an API from a trigger makes your updates slow and dependent on external service availability. A better flow:
  1. Log geocoding requests to a separate queue table in the trigger.
  2. Use a SQL Server Agent job or external service to process the queue asynchronously.
• Cache Geocode Results: Store lat/long values for previously processed Postcodes in a dedicated table to avoid redundant API calls (saves time and reduces costs).
• Validate Postcode Format: Add checks to ensure Postcodes are in a valid UK format before sending them to the API, reducing unnecessary errors.

内容的提问来源于stack exchange，提问作者M.Jones