带软删除的表的复杂唯一性校验触发器实现问题咨询

方案一：使用过滤唯一索引（最优） #

这是SQL Server原生支持的特性，直接创建仅针对未删除记录的唯一索引，完美解决软删除场景下的唯一性约束需求，性能优于触发器，且无需维护复杂的触发器逻辑。

创建语句：

CREATE UNIQUE NONCLUSTERED INDEX IX_Test_PN_RID_Active
ON Test (PN, RID)
WHERE Deleted = 0;

该索引会自动确保仅Deleted=0的记录中，PN和RID的组合是唯一的，已删除记录不会参与唯一性校验。后续表结构变更（比如新增字段）完全不影响索引有效性，无需修改任何代码。

方案二：改进触发器（兼容表结构变更） #

如果因版本限制无法使用过滤索引（如SQL Server 2008及更早版本），可修改触发器，通过动态SQL自动获取非自增列，避免硬编码列名，同时解决自增列报错问题：

CREATE TRIGGER T_PN ON Test INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    -- 检查是否存在未删除的重复记录
    IF EXISTS (
        SELECT 1 
        FROM Test t
        JOIN inserted i ON t.PN = i.PN AND t.RID = i.RID
        WHERE t.Deleted = 0
    )
    BEGIN
        RAISERROR('PN in use', 11, 1);
        RETURN;
    END

    -- 动态获取除自增列外的所有列
    DECLARE @columns NVARCHAR(MAX);
    SELECT @columns = STRING_AGG(QUOTENAME(name), ', ')
    FROM sys.columns
    WHERE object_id = OBJECT_ID('Test')
      AND is_identity = 0;

    -- 动态生成插入语句
    DECLARE @sql NVARCHAR(MAX) = N'INSERT INTO Test (' + @columns + N') SELECT ' + @columns + N' FROM inserted';
    EXEC sp_executesql @sql;
END

这个触发器会自动读取表中所有非自增列生成插入语句，后续表结构新增/删除字段时无需修改触发器代码，同时避免了直接SELECT *插入导致的自增列报错。

关于应用层校验的说明 #

应用层校验可作为辅助，但无法替代数据库层面的约束：高并发场景下，多个请求可能同时通过应用层校验，最终写入数据库时出现重复。数据库层面的约束是原子性的，能从根本上保证数据一致性。

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