INSTEAD OF UPDATES Trigger - is it possible?

Question

I'm making some settings for an outdated application built on SQL Server 2000, and, of course, I only want to make an absolute minimum in fear that it might just fall apart.

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

Moving users that are currently deleted is performed directly, however I want to be able to move any future users where the IsDeleted flag is set. I could use the standard AFTER trigger in the column, but I plan to add some restrictions to the tbUser table, which will violate this, what would I like to start and move the record to the archive table instead of starting my INSTEAD OF UPDATE?

I think my question is: is it possible to trigger the INSTEAD OF UPDATE trigger when updating a single column? This is what I still have:

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

If such an example (compatible with SQL 2000) would be very appreciated!

Answer 1

Using test UPDATE(columnname) , you can check the launch, regardless of whether the column has been updated (and then take specific actions), but you cannot start the trigger only when updating a specific column. It will fire as soon as the update is completed, regardless of which column was the object of the update.

So, if you think that you need to use the INSTEAD OF UPDATE trigger, you need to implement two types of actions in it:

1) insert into tbDeletedUsers + remove from tbUsers - when IsDeleted updated (or, more precisely, updated and set to 1 );

2) update tbUsers usually - when IsDeleted not updated (or updated, but not set to 1 ).

Since more than one row can be updated with one UPDATE , you may also need to consider that some rows may have IsDeleted set to 1 and others not.

I'm not a big fan of INSTEAD OF triggers, but if I really needed to use one for a task like yours, I could skip the UPDATE() test and implement the trigger as follows:

 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