SQL Server 2000 introduced a type of trigger called an
INSTEAD OF trigger. This type of trigger extends SQL Server’s trigger capabilities and provides an alternative to the
AFTER trigger that was heavily utilized in prior versions of SQL Server.
The name of the trigger gives you some insight into how this trigger operates: this trigger performs its actions instead of the action that fired it. This is much different from the
AFTER trigger, which performs its actions after the statement that caused it to fire has completed. This means you can have an
INSTEAD OF update trigger on a table that successfully completes but does not include the actual update to the table.
The basic syntax for creating an
INSTEAD OF trigger is as ...