Difference Between for and After Triggers

Difference between FOR and AFTER triggers?

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.

Trigger - Difference in FOR/AFTER/INSTEAD OF

There are Two types of Triggers,

1. Instead Of Triggers that fires Instead of the Triggering action
2. After or For Triggers that fires after the Triggering action.

Catalog View sys.triggers's column is_instead_of_trigger will tell you whether is an Instead of trigger or not, if value 0 then it is For or After Trigger, if value 1 then it is an Instead of Trigger

FOR/AFTER in SQL triggers

There is no difference between using FOR and AFTER.

I believe the original (pre 2000) syntax only used the FOR keyword. However, when INSTEAD OF triggers were introduced, the "FOR" keyword could seem quite confusing. "AFTER" more accurately conveys the type of trigger, and is more easily distinguished from "INSTEAD OF".

---

An INSTEAD OF trigger would be used if we wanted to transform what was inserted into the table, or prevent an insertion from taking place.

An AFTER trigger would more normally be used if we wanted to perform additional tasks, based on what has just occurred. For instance, you could have an "AFTER DELETE" trigger, that copied deleted rows into some kind of archive table. Basically, in an AFTER trigger, you more normally do still want the activity to occur.

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

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).

What is Different between After And For in Triggers?

FOR | AFTER

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.

FOR and AFTER are the same, INSTEAD OF is different, see Books Online for details
http://msdn.microsoft.com/en-us/library/ms189799(SQL.90).aspx

difference before and after trigger in oracle

First, I'll start my answer by defining trigger: a trigger is an stored procedure that is run when a row is added, modified or deleted.

Triggers can run BEFORE the action is taken or AFTER the action is taken.

BEFORE triggers are usually used when validation needs to take place before accepting the change. They run before any change is made to the database. Let's say you run a database for a bank. You have a table accounts and a table transactions. If a user makes a withdrawal from his account, you would want to make sure that the user has enough credits in his account for his withdrawal. The BEFORE trigger will allow to do that and prevent the row from being inserted in transactions if the balance in accounts is not enough.

AFTER triggers are usually used when information needs to be updated in a separate table due to a change. They run after changes have been made to the database (not necessarily committed). Let's go back to our back example. After a successful transaction, you would want balance to be updated in the accounts table. An AFTER trigger will allow you to do exactly that.

SQL Server - AFTER UPDATE vs FOR UPDATE?

after update and for update are synonyms for the same behavior, namely that the trigger fires after the update operation.

After Trigger with JOIN Tables

The following shows you how to handle the fact that Inserted might have multiple rows. This is really not ideal behaviour for a trigger, because you have to process the results RBAR (row by agonising row), which is slow by itself, let alone the fact that you are sending an email.

CREATE TRIGGER FullfillOrderQCResult
ON Table_1
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    -----DECLARE VARIABLES-----

    DECLARE @ACTIONPEFORMED varchar(max), @Id int;

    SELECT A.LotCode, A.LineNumber, CONVERT(bit, 0) Done, IDENTITY(int) id -- Use your own id if you have one, just need to uniquely identify each row.
    INTO #FullfillOrderQCResult_temp
    FROM Inserted AS A
    INNER JOIN Table_2 AS B ON A.LotCode = B.DocumentID2
    WHERE B.Result <> 1 and B.[Status] <> 3;

    WHILE EXISTS (SELECT 1 FROM #FullfillOrderQCResult_temp WHERE Done = 0) BEGIN
        SELECT TOP 1 @Id = id, @ACTIONPEFORMED = 
            N'Hello, ' + '<br>' + '<br>'
            + N'The following LOT NUMBER: ' + LotCode + ' has not been approved for this Item: ' + LineNumber
        FROM #FullfillOrderQCResult_temp
        WHERE Done = 0;

        EXEC MSDB.DBO.SP_SEND_DBMAIL
            @PROFILE_NAME = 'SQLMail',
            @RECIPIENTS = 'TEST@gmail.com',
            @SUBJECT = 'LOT NON-Approved',
            @BODY = @ACTIONPEFORMED,
            @IMPORTANCE = 'HIGH',
            @BODY_FORMAT = 'HTML';

        UPDATE #FullfillOrderQCResult_temp SET Done = 1 WHERE id = @Id;
    END;
END;

I don't know whether you would still want the concept of 'ALL GOOD MY FRIEND' because you could have none, some or all rows with issues. Anyway I assume print is only for debugging.

---

That said you would be much better off pushing an event into a queue and having a service process said event because triggers really should be as fast as possible. And adding an event to a queue could be handled in a set based manner e.g.

CREATE TRIGGER FullfillOrderQCResult
ON Table_1
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO MyEventQueue (A.LotCode, A.LineNumber) -- Any other information required to identify the records etc
        SELECT A.LotCode, A.LineNumber
        FROM Inserted AS A
        INNER JOIN Table_2 AS B ON A.LotCode = B.DocumentID2
        WHERE B.Result <> 1 and B.[Status] <> 3;
END;

---

Related Topics