SQL Server 觸發器規範 INSERT/UPDATE 值：INSTEAD OF 觸發器優雅解決日期截取、避免雙重操作

問題背景

表 A 主鍵含 Date 列（精度到天），但 DATETIME 存儲含時分秒。應用層可能傳入帶時間的值，需在入庫前自動截取日期部分。AFTER 觸發器 + UPDATE 會導致兩次寫入（INSERT → 觸發器 UPDATE），產生額外日志、觸發遞歸風險。

最佳方案：INSTEAD OF 觸發器

INSTEAD OF 觸發器完全接管 DML 操作，在觸發器內部執行真實寫入，可任意修改值，零額外寫入。

實戰代碼

CREATE TRIGGER trg_A_NormalizeDate
ON A
INSTEAD OF INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    
    -- INSERT 场景
    INSERT INTO A (ProductID, RetailerID, Date)
    SELECT 
        ProductID, 
        RetailerID, 
        CAST(Date AS DATE)  -- SQL Server 2008+ 直接轉 DATE 類型（僅日期）
        -- 兼容老版本：CONVERT(DATE, Date) 或 DATEADD(DAY, DATEDIFF(DAY, 0, Date), 0)
    FROM inserted;
    
    -- UPDATE 场景：同步更新 inserted 中的行
    UPDATE A
    SET 
        ProductID = i.ProductID,
        RetailerID = i.RetailerID,
        Date = CAST(i.Date AS DATE)
    FROM A a
    JOIN inserted i ON a.ID = i.ID;  -- 假設 ID 為標識列
END;
GO

關鍵點解析

• inserted 虛擬表保存客戶端提交的新值/修改後值。
• CAST(Date AS DATE) 直接截取日期部分，比字符串截取高效且類型安全。
• SET NOCOUNT ON 抑制「受影響行數」消息，減少網絡開銷。
• 同一觸發器處理 INSERT, UPDATE，邏輯集中維護。

對比：AFTER 觸發器 + UPDATE 的缺點

進階：MERGE 統一 UPSERT 邏輯（SQL Server 2008+）

CREATE TRIGGER trg_A_Upsert
ON A
INSTEAD OF INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    
    MERGE INTO A AS target
    USING (
        SELECT 
            ProductID, RetailerID, 
            CAST(Date AS DATE) AS Date,
            ID  -- 僅 UPDATE 時有值
        FROM inserted
    ) AS source
    ON target.ID = source.ID
    WHEN MATCHED THEN
        UPDATE SET 
            ProductID = source.ProductID,
            RetailerID = source.RetailerID,
            Date = source.Date
    WHEN NOT MATCHED BY TARGET THEN
        INSERT (ProductID, RetailerID, Date)
        VALUES (source.ProductID, source.RetailerID, source.Date);
END;

單語句同時處理新增/修改，代碼更簡潔、原子性更強。