MSSQL：更新语句避免了CHECK约束

MSSQL：更新语句避免了CHECK约束

存在一个已知问题，其中某些 *** 作将导致检查约束，从而导致绕过UDF。该错误已在Connect上列出（在被破坏之前，所有链接都被孤立），并且已经得到确认，但由于无法修复而被关闭。这意味着我们需要依靠变通办法。

我的第一个解决方法可能是代替更新触发器。感谢Martin使我保持诚实并进一步进行了测试-
我发现我无法防止在同一条语句中将两行更新为1。我已经纠正了逻辑并添加了一个事务来帮助防止出现竞争情况：

CREATE TRIGGER dbo.CheckJobOwners ON dbo.JobOwnersINSTEAD OF UPDATeASBEGIN  SET NOCOUNT ON;  BEGIN TRANSACTION;  UPDATE j SET IsActive = 1 --     FROM dbo.JobOwners AS j INNER JOIN inserted AS i    ON i.LogID = j.LogID    WHERe i.IsActive = 1 AND NOT EXISTS     (    -- since only one can be active, we don't need an expensive count:      SELECt 1 FROM dbo.JobOwners AS j2        WHERe j2.JPSID = i.JPSID        AND j2.IsActive = 1 AND j2.LogID <> i.LogID    )    AND NOT EXISTS     (    -- also need to protect against two rows updated by same statement:       SELECT 1 FROM inserted AS i2        WHERe i2.JPSID = i.JPSID        AND i2.IsActive = 1 AND i2.LogID <> i.LogID    );  -- *if* you want to report errors:  IF (@@ROWCOUNT <> (SELECt COUNT(*) FROM inserted WHERe IsActive = 1))    RAISERROR('At least one row was not updated.', 11, 1);  -- assume setting active = 0 always ok & that IsActive is not nullable  UPDATE j SET IsActive = 0 --     FROM dbo.JobOwners AS j INNER JOIN inserted AS i    ON j.LogID = i.LogID    WHERe i.IsActive = 0;  COMMIT TRANSACTION;ENDGO

（我之所以选择代替触发器而不是代替触发器的唯一原因是，您只更新需要更新的行，而不必在事实发生后回滚（这不会让您仅在无效情况下回滚无效的更新）。
-行更新））。

关于此问题，这里有很多很好的讨论：

https://web.archive.org/web/20171013131650/http://sqlblog.com/blogs/tibor_karaszi/archive/2009/12/17/be-
careful-with-constraints-calling-
udfs.aspx