The new INSTEAD OF functionality is not exactly like a trigger, but similar. Unlike AFTER triggers, the INSTEAD OF trigger replaces the incoming transaction data modification statement with its own code segment. This can be demonstrated with the following example. We first define a simple table:
CREATE TABLE TestTBL
id INT NOT NULL IDENTITY,
msg INT NOT NULL
Now we define an INSTEAD OF trigger.
CREATE TRIGGER trgIntercepted ON TestTBL INSTEAD OF INSERT
DECLARE @ret_msg AS VARCHAR(100);
SET @ret_msg =