INSTEAD OF UPDATE Trigger - is this possible?

Question

I am making some tweaks to a legacy application built on SQL Server 2000, needless to say I only want to do the absolute minimum in the fear that it may just all fall apart.

I have a large table of users, tbUsers, with a BIT flag for IsDeleted. I want to archive off all current and future IsDeleted = 1 user records into my archive table tbDeletedUsers.

Moving the currently deleted users is straight forward, however I want a way to move any future users where the IsDeleted flag is set. I could use a standard AFTER trigger on the column however I plan to add some constraints to the tbUser table that would violate this, what I'd like is for my INSTEAD OF UPDATE trigger to fire and move the record to archive table instead?

I guess my question is... is it possible to trigger an INSTEAD OF UPDATE trigger on the update of an individual column? This is what I have so far:

 CREATE TRIGGER trg_ArchiveUsers
 INSTEAD OF UPDATE ON tbUsers
 AS 
    BEGIN
      ...
    END
 GO

If so an example (SQL 2000 compatible) would be much appreciated!

Answer 1

Using the UPDATE(columnname) test, you can check in a trigger whether a specific column was updated (and then take specific actions), but you can't have a trigger fire only on the update of a specific column. It will fire as soon as the update is performed, regardless of the fact which column was the target of the update.

So, if you think you have to use an INSTEAD OF UPDATE trigger, you'll need to implement two kinds of actions in it:

1) insert into tbDeletedUsers + delete from tbUsers – when IsDeleted is updated (or, more exactly, updated and set to 1);

2) update tbUsers normally – when IsDeleted is not updated (or updated but not set to 1).

Because more than one row can be updated with a single UPDATE instruction, you might also need to take into account that some rows might have IsDeleted set to 1 and others not.

I'm not a big fan of INSTEAD OF triggers, but if I really had to use one for a task like yours, I might omit the UPDATE() test and implement the trigger like this:

CREATE TRIGGER trg_ArchiveUsers
ON tbUsers
INSTEAD OF UPDATE
AS
BEGIN
  UPDATE tbUsers
  SET
    column = INSERTED.column,
    …
  FROM INSERTED
  WHERE INSERTED.key = tbUsers.key
    AND INSERTED.IsDeleted = 0
  ;
  DELETE FROM tbUsers
  FROM INSERTED
  WHERE INSERTED.key = tbUsers.key
    AND INSERTED.IsDeleted = 1
  ;
  INSERT INTO tbDeletedUsers (columns)
  SELECT columns
  FROM INSERTED
  WHERE IsDeleted = 1
  ;
END