1

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

3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.