请求编写SQL Server DML INSERT触发器同步交叉引用表

需求匹配实现 #

针对TRIP表插入新行程时同步更新两个交叉引用表的需求，以下是符合业务规则的触发器代码，支持批量插入场景并保证数据一致性：

CREATE TRIGGER TRG_TRIP_AFTER_INSERT
ON TRIP
AFTER INSERT
AS
BEGIN
    -- 禁用受影响行数返回，减少网络传输开销
    SET NOCOUNT ON;

    -- 同步更新司机最新行程交叉表
    MERGE INTO XREF_TRIP_DRIVER AS Target
    USING (
        SELECT UID_DRIVER, UID_TRIP
        FROM INSERTED
    ) AS Source
    ON Target.UID_DRIVER = Source.UID_DRIVER
    WHEN MATCHED THEN
        -- 司机已有记录，覆盖为最新行程ID
        UPDATE SET Target.UID_TRIP = Source.UID_TRIP
    WHEN NOT MATCHED THEN
        -- 司机首次关联行程，插入新记录
        INSERT (UID_DRIVER, UID_TRIP)
        VALUES (Source.UID_DRIVER, Source.UID_TRIP);

    -- 同步更新车辆最新行程交叉表
    MERGE INTO XREF_TRIP_VEHICLE AS Target
    USING (
        SELECT UID_VEHICLE, UID_TRIP
        FROM INSERTED
    ) AS Source
    ON Target.UID_VEHICLE = Source.UID_VEHICLE
    WHEN MATCHED THEN
        -- 车辆已有记录，覆盖为最新行程ID
        UPDATE SET Target.UID_TRIP = Source.UID_TRIP
    WHEN NOT MATCHED THEN
        -- 车辆首次关联行程，插入新记录
        INSERT (UID_VEHICLE, UID_TRIP)
        VALUES (Source.UID_VEHICLE, Source.UID_TRIP);
END;
GO

核心细节说明 #

• MERGE语句的优势：原子性处理"存在则更新、不存在则插入"逻辑，避免分步骤操作的并发风险，同时天然支持INSERTED表的批量记录处理
• SET NOCOUNT ON：符合移动应用性能优化要求，减少不必要的网络返回数据
• AFTER INSERT时机：确保TRIP表的行程记录已成功写入后再更新交叉表，保证数据一致性
• 无冗余逻辑：因业务禁止删除行程，无需处理DELETE/UPDATE触发场景，仅聚焦INSERT操作

测试验证示例 #

-- 插入多条测试行程
INSERT INTO TRIP (UID_TRIP, UID_DRIVER, UID_VEHICLE)
VALUES ('TRIP001', 'DRIVER001', 'VEHICLE001'),
       ('TRIP002', 'DRIVER001', 'VEHICLE002');

-- 验证交叉表结果
SELECT * FROM XREF_TRIP_DRIVER WHERE UID_DRIVER = 'DRIVER001'; -- 应返回UID_TRIP='TRIP002'
SELECT * FROM XREF_TRIP_VEHICLE WHERE UID_VEHICLE IN ('VEHICLE001', 'VEHICLE002'); -- 分别对应TRIP001、TRIP002

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