title: How to prevent updates to a table, with an exception for one situation
body: <p>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:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>However, there is one more wrinkle. The Item table also has a DATETIME Modified column, and a trigger that updates it.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>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? </p>
  
  <p>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.</p>