How Instead of trigger Sql server used with Inserted table?
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. What is instead of trigger SQL Server?
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.
Instead of trigger SQL Server:
An INSTEAD OF trigger is a trigger that allows you to skip an INSERT , DELETE , or UPDATE statement to a table or a view and execute other statements defined in the trigger instead. In other words, an INSTEAD OF trigger skips a DML statement and execute other statements.