what is the difference between INSTEAD OF and AFTER trigger in SQL Server?

Question

What is the difference between INSTEAD OF and AFTER trigger in SQL Server?

INSTEAD OF trigger invoked before unique key constraint, will the AFTER trigger invoke after unique key constraint?

Answer 1

AFTER trigger fires after a DML operation. INSTEAD OF trigger fires INSTEAD OF a DML operation.

Big difference. INSTEAD OF allows you to override functionality. One common place I use it is to create updateable views. Sometimes a view may not be key preserved, but as the designer you may know which base table(s) you want to update so you can do it by writing specific logic to do the update behind the scenes. An alternative is just to write a stored procedure and force the developers to call those procedures instead of performing DML on a view, but DML on views is a nice abstraction, in my opinion, because developers can treat views like tables. That is how relational design is meant to be.

When you say "after unique key constraint", this is confusing, as that is not a verb, it is a noun (constraint). If you mean constraint violation, then the AFTER trigger will happen after the DML succeeds, so obviously it has to happen after any violations (which will force a rollback).