Menu
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?
340
AFTER triggers are fired after an insert, update, or delete operation. INSTEAD OF triggers fire before an insert, update, or delete operation. INSTEAD OF triggers replace the original operation.
An INSTEAD OF trigger is an SQL trigger that is processed “instead of” an SQL UPDATE, DELETE or INSERT statement. Unlike SQL BEFORE and AFTER triggers, an INSTEAD OF trigger can be defined only on a view, not a table.
A BEFORE triggered action executes before the triggering statement , that is, before the occurrence of the trigger event. An AFTER triggered action executes after the action of the triggering statement is complete. BEFORE and AFTER triggered actions execute even if the triggering statement does not process any rows.
An INSTEAD OF trigger is a trigger that allows you to update data in tables via their view which cannot be modified directly through DML statements. When you issue a DML statement such as INSERT , UPDATE , or DELETE to a non-updatable view, Oracle will issue an error.
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, or implement functionality that otherwise isn't supported. The 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.
Regarding "after unique key constraint", the AFTER trigger will happen after the DML succeeds, so it will happen after any violations (which will force a rollback).
58
After trigger executes after data modification while Instead of trigger executes prior to data modification.
24
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
