Using the "Instead Of" triggers

Found a nice little SQL instruction the other day called INSTEAD OF, this gem allows you to override the INSERT, UPDATE or DELETE methods on a view or table and implement your own code.... Lets say for example you have a table of employees and you want to make sure that when the employees are deleted from the database they aren't actually deleted only flagged so they don't show up?  You could move the items into another table using a DELETE trigger or you could write something up in the calling app to do it for you but for supreme ease of use the INSTEAD OF trigger comes up trumps... behold

CREATE TRIGGER myDelete ON dbo.Employees INSTEAD OF DELETE
AS
BEGIN
   -- The table [Deleted] will contain all the records that have been set for deletion although you can't alter
   -- these directly so we join them to the real table
    UPDATE dbo.[Employees] SET dbo.[Employees].[Deleted] = 1
       FROM dbo.[Employees] INNER JOIN [Deleted] ON dbo.[Employees].[ID]=[Deleted].[ID]
END

As usual you are given a table (in this case DELETED) which holds the records of the items that have been marked for deletion. You're not allowed to modify this table directly so you have to inner join back to the original table and do what you need to. As you can see from the example above we are merely setting a flag column called [Deleted] rather than actually deleting the record, something that would not be possible with the DELETE Trigger alone, all hail INSTEAD OF.