如何防止对表的更新（一种情况例外） [英] How to prevent updates to a table, with an exception for one situation

问题描述

我有一个表，其中包含可以成为账单一部分的记录。我可以说出哪些已经属于账单，因为该表具有BillId列，该列在发生时由应用程序代码更新。我想防止更新具有非空BillId的任何记录。我认为以下内容应解决此问题：

 创建触发器[Item_Update_AnyBilled] 
开[dbo] 。[项目] 
用于更新
，因为
开始
设置为NOCOUNT ON； 
 DECLARE @AnyBilled BIT； 
 
选择顶部（1）@AnyBilled = 1 
从插入的i 
 JOIN删除d ON i.ItemId = d.ItemId 
 d.BillId不为空; 
 
如果COALESCE（@AnyBilled，0）= 1开始
 RAISERROR（2870486，16，1）; -无法更新属于帐单的记录。 
回滚交易； 
 END； 
 END； 
  

但是，还有更多皱纹。 Item表还具有一个DATETIME Modified列，以及一个对其进行更新的触发器。

 创建触发器[dbo]。Item_Update_Modified
 ON [dbo]。[项目] 
更新后
 AS 
开始
设为NOCOUNT； 
 
更新a 
 SET修改= getdate（）
从在i上插入的JOIN插入i.ItemId = a.ItemId 
 END 
  

有了这些触发器，向物料清单添加项目始终会引发RAISERROR。大概是因为填充BillId时，Item_Update_AnyBilled允许它通过，因为delete.BillId为NULL，但随后又执行了Item_Update_Modified，并且该次要更改导致Item_Update_AnyBilled再次被执行，这次被删除了.BillId不再为NULL。 / p>

除了填充BillId或仅对Modified列进行更改的情况之外，如何防止对Item表进行更新？

我希望解决方案不需要我比较每列的插入和删除值（或使用COLUMNS_UPDATED（）），因为这会创建维护项问题（每次在表中添加或删除新列时，都必须记住要更新触发器）。我正在使用SQL Server 2005。

解决方案

为什么不使用 INSTEAD OF 触发？它需要做更多的工作（即重复的 UPDATE 语句），但是任何时候您都可以阻止工作，而不是让它发生然后回滚，您将

 创建触发器[dbo]。[Item_BeforeUpdate_AnyBilled] 
开[dbo]。[Item] 
更新
为
开始
设为NOCOUNT； 
 
如果存在
（
选择1从插入的i 
 JOIN中删除为d ON i.ItemId = d.ItemId 
 WHERE d.BillId为NULL -以前是NULL，现在可能不是NULL 
）
 BEGIN 
 UPDATE src 
 SET col1 = i.col1-，...其他列
 ModifiedDate = CURRENT_TIMESTAMP-这消除了对其他触发器
从dbo.Item AS src 
 INNER JOIN插入的需求i i 
 ON i.ItemId = src.ItemId 
 AND（确定是否至少一列已更改）； 
 END 
 ELSE 
开始
 RAISERROR（...）; 
 END 
 END 
 GO 
  

这并不完美。我遗漏的条件由于以下原因而被忽略：确定列值是否已更改可能很复杂，因为它取决于数据类型，列是否可以为NULL等。AFAIK内置触发器函数只能告诉您是否指定了特定的列，而不能告诉值实际上是否从以前更改过。

EDIT ，因为您只在乎由于after触发器而更新的其他列，我认为以下 INSTEAD OF 触发器可以替换您现有的两个触发器，并且还可以处理一次更新的多行（有些

 创建触发器[dbo]。[Item_BeforeUpdate_AnyBilled] 
开[dbo]。[Item ] 
更新
，因为
开始
设置为NOCOUNT； 
 
更新src SET col1 = i.col1-，...其他列，
 ModifiedDate = CURRENT_TIMESTAMP 
 FROM dbo.Item AS src 
 INNER JOIN插入为AS i 
 src.ItemID = i.ItemID 
内部联接已删除，因为d 
 i.ItemID = d.ItemID 
 d.BillID为NULL； 
 
如果@@ ROWCOUNT = 0 
开始
 RAISERROR（...）; 
 END 
 END 
 GO 
  

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;

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

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.

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?

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.

解决方案

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

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 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

这篇关于如何防止对表的更新（一种情况例外）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！