Instead of insert trigger VS For insert Triggers in SQL server
My question is why insert trigger is not fired from instead of trigger? It should because it is also using insert query in it. Why TestTable get the new inserted row?


The INSTEAD OF trigger replaces the original insert. So the code:

Insert into dbo.TestTable (Name,Age,DOB,Address) 
Select Name,Age,DOB,Address From inserted; 


...is executed instead of the original insert:

Insert into dbo.TestTable values ('Waleed',24,'10-2-2015','Lahore');


The actions in the instead of trigger are completed before constraints are checked, and before any AFTER triggers fire.

When the instead of trigger completes, a row has been added to the TestTable and CopyTable tables. Note though that the transaction is still active at this point, so the changes are not yet permanent.

Next, the after trigger fires. It sees the name 'Waleed' in the inserted table, prints a message, but does not rollback the changes made so far in the transaction (including the rows added by the instead of trigger). No row is added to the AuditTable.

If the condition that checked for the name 'Waleed' also performed a rollback, everything would be rolled back, and no rows would be added to any table.