After Trigger and Instead of Trigger

Question

Hi friends what is after trigger and instead of trigger ? In which case we use them?

Aarti · Answer

Hi Akshay AFTER TRIGGER: AFTER specifies that the trigger is fired only when all operations specified in the triggering SQL statement have executed successfully. All referential cascade actions and constraint checks also must succeed before this trigger fires. AFTER is the default when FOR is the only keyword specified. AFTER triggers cannot be defined on views. INSTEAD OF TRIGGER: Specifies that the DML trigger is executed instead of the triggering SQL statement therefore overriding the actions of the triggering statements. INSTEAD OF cannot be specified for DDL or logon triggers. At most one INSTEAD OF trigger per INSERT UPDATE or DELETE statement can be defined on a table or view. However you can define views on views where each view has its own INSTEAD OF trigger. INSTEAD OF triggers are not allowed on updatable views that use WITH CHECK OPTION. SQL Server raises an error when an INSTEAD OF trigger is added to an updatable view WITH CHECK OPTION specified. The user must remove that option by using ALTER VIEW before defining the INSTEAD OF trigger.

Prabhu Raja · Answer

Hi The CREATE TRIGGER statement provides for two types of triggers: AFTER triggers and INSTEAD OF triggers. Both types of triggers can be defined to fire for an insert update or delete operation. If an action query has an AFTER trigger the trigger fires after the successful completion of action query. If an action query has an INSETEAD OF trigger the trigger is fired instead of the action query. Refer this : http://www.c-sharpcorner.com/uploadfile/shashikantray/create-delete-and-update-triggers-in-a-database/