MSSQL 触发器或约束，用于阻止或允许基于列值进行更新/删除

Question

我有一个客户表，我想在行的状态列不是 1 时阻止更新/插入。

在服务器上创建这种功能的最佳方法是什么？

Answer 1

可以创建存储过程并强制使用它来更新表。

create procedure updateCustomers @custID int,@newValue nvarchar(50) 
with execute as superUser 
as
update customers
set someColumn=@newValue
where custID=@custID and status=1

Answer 2

1）如果要在新Status差异时阻止任何插入/更新操作，则1然后以下触发器将阻止此类操作。无论如何，这个要求是没有意义的，因为使用检查约束非常简单：ALTER TABLE dbo.Customer ADD CONSTRAINT ... CHECK ([Status] <> 1)

CREATE TRIGGER dbo.trgIU_Customer_PreventIU
ON dbo.Customer
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON
    IF EXISTS(SELECT * FROM inserted i WHERE i.[Status] IS NOT NULL AND i.[Status] <> 1)
    BEGIN
        RAISERROR('Can''t insert or update rows with Status <> 1', 16, 1)
        ROLLBACK
    END
END

2）但是，如果您想在当前[Status]为1时简单地避免任何更新，则可以使用以下触发器：

CREATE TRIGGER dbo.trgU_Customer_PreventU
ON dbo.Customer
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON
    IF EXISTS(SELECT * FROM deleted d WHERE d.[Status] = 1)
    BEGIN
        RAISERROR('Can''t update rows with Status = 1', 16, 1)
        ROLLBACK
    END
END

Answer 3

你可以这样尝试：

CREATE TRIGGER [dbo].[PreventUpdate]
ON [dbo].[Customers]
INSTEAD OF UPDATE
AS 
BEGIN
  SET NOCOUNT ON;
  IF EXISTS 
  (
     SELECT 1 FROM Customers c
       WHERE c.Status <> 1 )
  BEGIN
     RAISERROR(...);
  END
  ELSE
  BEGIN
     --You update code
  END
END
GO

Answer 4

最好的方法是什么

请勿将触发器用于此目的。 触发器的存在是为了强制引用完整性，将它们用于其他目的会导致眼泪。

拒绝用户对表的权限，并改为提供存储过程，例如 doublet28 建议。 这使您有机会提供有意义的错误消息，并使 DBA 可以自由地更新表，而不会触发触发器（迟早需要触发器）。