Instead of Triggers

       INSTEAD OF triggers give you the ability to evaluate the changes that would have taken place if the data modification statement had actually executed. This trigger fires before the changes happen, but the rows that would have been affected are available in the Inserted and Deleted tables.

The AFTER trigger might appear less efficient because it sometimes needs to undo work that has already been done. If you think your table will have numerous violations that your trigger will need to correct, you might want to choose the INSTEAD OF trigger. However, if the vast majority of your updates will be acceptable, the INSTEAD OF trigger will have more work to do and thus be less efficient.

 You can have only one INSTEAD OF trigger on each table for each action (INSERT, UPDATE, and DELETE), and you can't set a firing order for INSTEAD OF triggers.  Another difference is that INSTEAD OF triggers can never be recursive, regardless of the setting of the recursive triggers database option.

Also, you can't combine INSTEAD OF triggers and foreign keys with CASCADE on a table. For example, if the Sales table has a foreign-key constraint that references the Titles table and specifies CASCADE as the response to DELETE operations on Titles, you'll get an error message if you try to create an INSTEAD OF trigger for DELETE on Sales. However, you can have INSTEAD OF triggers for INSERT or UPDATE. Similarly, if you already have an INSTEAD OF trigger on Sales, you can't alter the table to add a foreign-key constraint with the CASCADE action for the same data-modification operation.