How to prevent updates to a table, with an excepti

Question

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.

Answer 1

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