Triggers

  • What is the difference between Instead and After Trigger in sql server ?

    Give examples on Instead and After Trigger practically?

  • This is from BOL.

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

    This is an example for after trigger - sends an email message to specific person when a customer table has changed.

    CREATE TRIGGER reminder2

    ON Sales.Customer

    AFTER INSERT, UPDATE, DELETE

    AS

    EXEC master..xp_sendmail 'MaryM',

    'Don''t forget to print a report for the sales force.'

    GO

  • AFTER triggers fire after changes are applied to the underlying table, INSTEAD OF triggers fire when you try to update the underlying table, but, instead of applying changes, only the trigger is fired.

    Example: suppose you have a Customers table and you don't want negative customerId

    CREATE TABLE Customers (

    Id int,

    Name varchar(50)

    )

    -- Turn negative id to positive: applies changes to the data already inserted

    CREATE TRIGGER TR_Customer_After

    ON Customers

    AFTER INSERT

    AS

    BEGIN

    UPDATE Customers

    SET Id = (-1) * Id

    FROM Customers A

    INNER JOIN Inserted B

    ON A.Id = B.Id

    WHERE B.Id < 0

    END

    -- Throws exception for negative id

    CREATE TRIGGER TR_Customer_Instead

    ON Customers

    INSTEAD OF INSERT

    AS

    BEGIN

    IF EXISTS (

    SELECT 1

    FROM Inserted

    WHERE Id < 0

    )

    RAISERROR('No negative ids allowed!!',10,1)

    ELSE

    INSERT INTO Customers SELECT * FROM Inserted

    END

    As you can see, if you use INSTEAD OF triggers, the trigger code will be responsible for copying the rows from inserted to the physical table.

    -- Gianluca Sartori

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply