SQL Server 條件唯一約束：a=1 時 b,c,d 唯一，觸發器實現方案

業務需求

表包含字段 a(bit), b,c,d,e。要求：當 a=1 時，b+c+d 組合必須唯一；a=0 時允許重複。需在 DDL 層面保證。

方案：INSTEAD OF 觸發器

SQL Server 不支持過濾唯一索引（SQL Server 2008+ 支持 WHERE a=1 的過濾索引，但僅限非空列且無法直接覆蓋多列組合），通用方案是觸發器。

完整可運行腳本

-- 建表示例
CREATE TABLE dbo.TestAsk (
    id INT PRIMARY KEY IDENTITY(1,1),
    a BIT NOT NULL,
    b VARCHAR(50), c VARCHAR(50), d VARCHAR(50), e VARCHAR(50)
);
GO

-- 觸發器：INSERT/UPDATE 時檢查
CREATE TRIGGER dbo.trig_TestAsk_I_U ON dbo.TestAsk
FOR INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    -- 存在：a=1 且 b,c,d 相同但主鍵不同 → 違規
    IF EXISTS (
        SELECT 1 FROM TestAsk T1
        WHERE EXISTS (
            SELECT 1 FROM INSERTED T2
            WHERE T2.a = 1
              AND T1.b = T2.b AND T1.c = T2.c AND T1.d = T2.d
              AND T1.id != T2.id
        )
    )
    BEGIN
        RAISERROR('a=1時，b,c,d必須唯一!', 16, 1);
        ROLLBACK TRAN;
        RETURN;
    END
END
GO

-- 測試用例
INSERT INTO TestAsk (a,b,c,d) VALUES (0,'b','c','d'); -- 允許重複
INSERT INTO TestAsk (a,b,c,d) VALUES (0,'b','c','d'); -- 允許
INSERT INTO TestAsk (a,b,c,d) VALUES (1,'b','c','d'); -- 唯一
UPDATE TestAsk SET a=1 WHERE id=2; -- 若導致重複則報錯回滾

關鍵點

1. INSERTED 虛表包含新/修改行，與主表 TestAsk 自連接比對。
2. T1.id != T2.id 排除自我比對（更新同一行時）。
3. RAISERROR + ROLLBACK 終止事務並拋出用戶可捕獲的錯誤。
4. 性能：針對 b,c,d 建立非聚簇索引加速觸發器查詢。

替代方案（SQL Server 2008+）

CREATE UNIQUE NONCLUSTERED INDEX UX_TestAsk_Conditional
ON dbo.TestAsk (b, c, d)
WHERE a = 1; -- 過濾唯一索引，原生支持，性能最優

若版本支持，強烈推薦過濾索引，無需觸發器維護成本。