I have created an AFTERINSERT trigger & INSTEADOFINSERT trigger on tables.
Code:
create trigger trig_AFTERINSERT
on original
FOR INSERT AS
BEGIN
INSERT trigger_original(Roll,UserName,Password)
SELECT ROLL,Name,Name+'124'
FROM INSERTED
END
Code:
insert into original values(1,'A')
WHen I execute the above statemnet, 2 Rows get inserted. ONE ROW IS INSERTED in TABLE - original, ANOTHER ROW GET INSERTED IN TABLE - trigger_original.

NOW I HAVE CREATED an INSTEADOFINSERT Trigger on the same tables.
Code:
create trigger trig_INSTEADOFINSERT
on original
INSTEAD OF INSERT
AS
INSERT trigger_original(Roll,UserName,Password)
SELECT ROLL,Name,Name+'124'
FROM INSERTED
Code:
insert into original values(2,'B')
WHen the above insert statement is executed, I get message 2 Rows Affected.
But when i select the records

Code:
Select * from original
- IT CONTAINS ONLY THE ONE ROW ( WITH ROLL NO 1)
Code:
Select * from trigger_original
( TWO ROWS ARE THERE - CORRECT)

WHy the row is not inserted into table original that is inserted using the INSTEADOF_INSERT Trigger.