如何防止更新表，一种情况除外

2022-01-01 00:00:00 sql-server-2005 sql-server triggers

我有一个表格，其中包含可以成为账单一部分的记录.我可以分辨哪些已经是账单的一部分，因为该表有一个 BillId 列，当发生这种情况时，应用程序代码会更新该列.我想阻止更新任何具有非空 BillId 的记录.我认为以下应该解决这个问题:

I have a table that contains records that can become part of a bill. I can tell which ones are already part of a bill because the table has a BillId column that gets updated by the application code when that happens. I want to prevent updates to any record that has a non-null BillId. I'm thinking that the following should take care of that:

CREATE TRIGGER [Item_Update_AnyBilled]
ON [dbo].[Item]
FOR UPDATE
AS 
BEGIN
SET NOCOUNT ON;
DECLARE @AnyBilled BIT;

SELECT  TOP(1) @AnyBilled = 1
  FROM  inserted i
        JOIN deleted d ON i.ItemId = d.ItemId
 WHERE  d.BillId IS NOT NULL; 

IF COALESCE(@AnyBilled, 0) = 1 BEGIN
    RAISERROR(2870486, 16, 1);  -- Cannot update a record that is part of a bill.
    ROLLBACK TRANSACTION;
END;
END;

然而，还有一条皱纹.Item 表还有一个 DATETIME Modified 列，以及一个更新它的触发器.

However, there is one more wrinkle. The Item table also has a DATETIME Modified column, and a trigger that updates it.

CREATE TRIGGER [dbo].Item_Update_Modified 
ON  [dbo].[Item] 
AFTER UPDATE
AS 
BEGIN
SET NOCOUNT ON;

UPDATE a
   SET Modified = getdate()
  FROM Item a JOIN inserted i ON i.ItemId = a.ItemId
END

有了这些触发器，向账单添加项目总是会导致 RAISERROR 触发.大概是因为在填充 BillId 时，Item_Update_AnyBilled 允许它通过，因为已删除的.BillId 为 NULL，但是 Item_Update_Modified 然后被执行，并且二次更改导致 Item_Update_AnyBilled 再次执行，而这次被删除.BillId 不再是 NULL.

With these triggers in place, adding an Item to a Bill always causes the RAISERROR to fire. Presumably because when the BillId is populated, Item_Update_AnyBilled lets it through because the deleted.BillId is NULL, but the Item_Update_Modified then gets executed, and that secondary change causes Item_Update_AnyBilled to get executed again, and this time deleted.BillId is no longer NULL.

如何防止更新 Item 表，除非正在填充 BillId 或唯一的更改是对 Modified 列进行更改?

How can I prevent updates to the Item table except in the case where the BillId is being populated or when the only change is to the Modified column?

我更喜欢不需要我比较每列的插入和删除值(或使用 COLUMNS_UPDATED())的解决方案，因为这会产生维护问题(有人必须记住更新触发器)表中添加或删除新列的时间).我使用的是 SQL Server 2005.

I'd prefer a solution that didn't require me to compare the inserted and deleted values of every column (or use COLUMNS_UPDATED()) as that would create a maintenance issue (someone would have to remember to update the trigger any time a new column is added to or deleted from the table). I am using SQL Server 2005.

推荐答案

为什么不使用 INSTEAD OF 触发器?它需要更多的工作(即重复的 UPDATE 语句)，但任何时候你可以阻止工作，而不是让它发生然后回滚它，你会变得更好.>

Why not use an INSTEAD OF trigger? It requires a bit more work (namely a repeated UPDATE statement) but any time you can prevent work, instead of letting it happen and then rolling it back, you're going to be better off.

CREATE TRIGGER [dbo].[Item_BeforeUpdate_AnyBilled]
ON [dbo].[Item]
INSTEAD OF UPDATE
AS 
BEGIN
  SET NOCOUNT ON;

  IF EXISTS 
  (
     SELECT 1 FROM inserted i
       JOIN deleted AS d ON i.ItemId = d.ItemId
       WHERE d.BillId IS NULL -- it was NULL before, may not be NULL now
  )
  BEGIN
     UPDATE src 
       SET col1 = i.col1 --, ... other columns
          ModifiedDate = CURRENT_TIMESTAMP -- this eliminates need for other trigger
       FROM dbo.Item AS src
       INNER JOIN inserted AS i
       ON i.ItemId = src.ItemId
       AND (criteria to determine if at least one column has changed);
  END
  ELSE
  BEGIN
     RAISERROR(...);
  END
END
GO

这并不完美.我遗漏的标准是有原因的:确定列值是否已更改可能很复杂，因为它取决于数据类型、列是否可以为 NULL 等.AFAIK 内置触发器函数只能判断是否指定了某个列，而不能判断该值是否实际上比之前发生了变化.

This doesn't fit perfectly. The criteria I've left out is left out for a reason: it can be complex to determine if a column value has changed, as it depends on the datatype, whether the column can be NULL, etc. AFAIK the built-in trigger functions can only tell if a certain column was specified, not whether the value actually changed from before.

EDIT 考虑到您只关心由于 after 触发器而更新的其他列，我认为以下 INSTEAD OF 触发器可以替换您的两个现有触发器并处理一次更新的多行(有些不符合您的标准):

EDIT considering that you're only concerned about the other columns that are updated due to the after trigger, I think the following INSTEAD OF trigger can replace both of your existing triggers and also deal with multiple rows updated at once (some without meeting your criteria):

CREATE TRIGGER [dbo].[Item_BeforeUpdate_AnyBilled]
ON [dbo].[Item]
INSTEAD OF UPDATE
AS 
BEGIN
  SET NOCOUNT ON;

  UPDATE src SET col1 = i.col1 --, ... other columns,
     ModifiedDate = CURRENT_TIMESTAMP
     FROM dbo.Item AS src
     INNER JOIN inserted AS i
     ON src.ItemID = i.ItemID
     INNER JOIN deleted AS d
     ON i.ItemID = d.ItemID 
     WHERE d.BillID IS NULL; 

  IF @@ROWCOUNT = 0
  BEGIN
    RAISERROR(...);
  END
END
GO

相关文章