Hi friends
what is after trigger and instead of trigger ? In which case we use them?
Loading
Know the answer? Post it — somebody with the same question will find it here.
Sign in to answer this question
It is the same account you read, post and publish with — and you will come straight back to this page.
AartiPosted Dec 21, 2011, 1:46 AM
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 RajaPosted Dec 20, 2011, 11:36 PM
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/