我有一种情况，我需要根据某些条件（实际上是外部表中的标志）来写保护表的某些行。考虑这个模式：
CREATE TABLE Batch (
Id INT NOT NULL PRIMARY KEY,
DateCreated DATETIME NOT NULL,
Locked BIT NOT NULL
)
CREATE UNIQUE INDEX U_Batch_Locked ON Batch (Locked) WHERE Locked=0
CREATE TABLE ProtectedTable (
Id INT NOT NULL IDENTITY PRIMARY KEY,
Quantity DECIMAL(10,3) NOT NULL,
Price Money NOT NULL,
BatchId INT NULL)
ALTER TABLE ProtectedTable ADD CONSTRAINT FK_ProtectedTable_Batch FOREIGN KEY (BatchId) REFERENCES Batch(id)
如果该行链接到 Locked=1 的 Batch，我想防止更改 Quantity 和 Price。我还需要防止该行被删除。
注意：U_Batch_Locked 确保在任何时候最多可以解锁一个批次。
我曾尝试使用触发器 (yikes)，但这会导致更多问题，因为触发器会回滚事务。更新通常发生在 C# 客户端中，该客户端在单个事务中执行多个更新（对多个表）。不管错误如何，客户端都会继续更新，如果发生任何错误，则在事务结束时将其回滚。通过这种方式，它可以收集所有/大部分约束违规，并让用户在尝试再次保存更改之前修复它们。但是，由于触发器在不满足约束时回滚事务，因此后续更新会启动它们自己的自动事务并实际上已提交。客户端在批处理结束时发出的最终回滚完全失败。
我还发现了这篇博客文章：使用 ROWVERSION 来执行业务规则，虽然这似乎是我所需要的，但它需要外键具有相反的方向（即受保护的表是父子关系中的父表，而在我的如果受保护的表是子表）
有没有人做过这样的事情？这似乎是一个并不少见的业务需求，但我还没有看到合适的解决方案。我知道我可以在客户端实现这一点，但这留下了错误的空间：如果有人使用直接 SQL（错误地）更改这些怎么办，如果升级/迁移脚本或客户端本身有错误并且未能执行约束？
经过大量搜索和反复试验，我最终使用INSTEAD OF触发器来检查约束，并在必要时引发错误并跳过操作。参考原始问题中的架构，以下是所需的触发器：
更新触发器：
CREATE TRIGGER ProtectedTable_LockU ON ProtectedTable
INSTEAD OF UPDATE
AS
SET NOCOUNT ON;
IF UPDATE(Quantity) OR UPDATE(Price)
AND EXISTS(
SELECT *
FROM inserted i INNER JOIN deleted d ON d.Id = i.Id
LEFT JOIN Batch b ON b.Id = d.BatchId
WHERE b.Locked <> 0 AND
(i.Quantity <> d. Quantity OR i.Price <> d.Price))
BEGIN
RAISERRROR('[CK_ProtectedTable_DataLocked]: Attempted to update locked data', 16, 1)
RETURN
END
UPDATE pt
SET Quantity = i.Quantity,
Price = i.Price,
BatchId = i.BatchId
FROM ProtectedTable pt
INNER JOIN deleted d ON d.Id = pt.ID
INNER JOIN inserted i ON i.Id = d.Id
删除触发器：
CREATE TRIGGER ProtectedTable_LockD ON ProtectedTable
INSTEAD OF DELETE
AS
SET NOCOUNT ON;
IF EXISTS(
SELECT *
FROM deleted d
LEFT JOIN Batch b ON b.Id = d.BatchId
WHERE b.Locked <> 0)
BEGIN
RAISERRROR('[CK_ProtectedTable_DataLocked]: Attempted to delete locked data', 16, 1)
RETURN
END
DELETE pt
FROM ProtectedTable pt
INNER JOIN deleted d ON d.Id = pt.ID
当然，可以创建一个视图并将触发器放在视图上。这样你也可以避免INSTEAD OF触发器和FOREIGN KEY ON DELETE/ON UPDATE规则之间的冲突。
它很难看，我根本不喜欢它，但它是唯一实际有效且行为或多或少类似于常规数据库约束的解决方案（在修改数据之前检查数据库约束）。我还没有对此进行广泛的测试，我不确定它没有问题。我也担心竞争条件（例如，如果在触发器执行期间修改了 Batch 怎么办？我应该/可以包含锁定提示以确保完整性吗？）
PS：不管应用设计有多糟糕，这仍然是一个合理的要求。在某些情况下，它也是合法的（您必须证明在满足某些条件后无法更改某些记录）。这就是为什么在 SO 中时不时会弹出这个问题，并且没有明确的解决方案。我发现这个解决方案比使用AFTER触发器更好，因为它的行为更像是一个普通的数据库约束。
希望这可以帮助处于类似情况的其他人。
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句