1. Action Query: In the "After Trigger" the table data is affected after the execution of the action query whereas the table data isn't affected after the execution of an action query in an "Instead of Trigger".
Here, I'll discuss three types of trigger: external, internal, and synthetic. These each have different strengths and weaknesses, and each can be used to design great behaviors that form lasting habits. Let's look more closely at each type of trigger.
AFTER Triggers AFTER triggers execute the trigger action after the triggering statement is executed. AFTER triggers are used in the following situations: AFTER triggers are used when you want the triggering statement to complete before executing the trigger action.
There is no difference, they do the same thing.
CREATE TRIGGER trgTable on dbo.Table FOR INSERT,UPDATE,DELETE
Is the same as
CREATE TRIGGER trgTable on dbo.Table AFTER INSERT,UPDATE,DELETE
An INSTEAD OF
trigger is different, and fires before and instead of the insert and can be used on views, in order to insert the appropriate values into the underlying tables.
@Ben is absolutely right.
Here is MSDN article Exploring SQL Server Triggers
A paragraph from the article:
That syntax is also acceptable in older versions of SQL Server. However, now that there are two types of triggers in SQL Server 2000, I prefer to refer to FOR triggers as AFTER triggers. Thus, for the remainder of this article I will refer to either AFTER or INSTEAD OF triggers.
Like the AFTER trigger you saw earlier, this trigger prevents changes from being made to the lastname field. However, it implements this business rule differently than the previous example. Because the INSTEAD OF trigger fires in place of the UPDATE statement, the INSTEAD OF trigger then evaluates if the business rule test passes or not. If the business rule test passes, in order for the update to occur the INSTEAD OF trigger must explicitly invoke the UPDATE statement again.
AFTER specifies that the DML 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 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.
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql
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