SQL Server Trigger – Using Instead of Trigger with Inserted Table

sql servertrigger

I have this trigger: 

create TRIGGER Emp_log on Emp

Instead of insert as
SELECT * FROM INSERTED

Then i make this insert: 

insert into emp
values(99,'bbb','aaa','99','1900-09-09',99,99)

As a result no line was inserted in the emp table, as it should be.
But anyway i got the display of the inserted row with the command SELECT * FROM INSERTED.

The question is: The "INSERTED" table contains what is written in the insert statement, or contains rows actually inserted?

Because I expected no insert, and then no display from SELECT * FROM INSERTED.

Thank You

Best Answer

Correct, that is because the Trigger is just part of the entire transaction. The INSERTED table will hold the records that would have been inserted.

The difference between AFTER triggers, which may be causing this confusion, is that INSTEAD OF doesn't verify if the row insert would have caused failures due to any constraints present on the table. If an insert causes a PK violation for instance, the INSTEAD OF trigger would still fire, the AFTER trigger would not.

Typically, Instead Of triggers are used to perform additional validation or calculations on the data (from the inserted table) before doing the final insert or rollback or raiserror depending on the business logic you are trying to implement.

Related Question