INSTEAD OF and AFTER Triggers in SQL Server

Hello Folks,

You all would have heard this, I think so. Well if not, then you need not have to worry anymore because I am going to give a brief on this.

Triggers are special stored procedures that are attached to a table and fired when certain data modification operations hits that table.

There are basically two types of triggers:

  • INSTEAD OF
  • AFTER

Will discuss with each of them separately;

INSTEAD OF Trigger:

  • INSTEAD OF trigger causes the INSERT, UPDATE, or, DELETE operation to be cancelled.
  • Due to this the SQL command submitted to SQL Server is discarded by the INSTEAD OF trigger.
  • In-fact the code within the INSTEAD OF trigger is executed instead of the submitted SQL command.
  • The INSTEAD OF trigger might be programmed to repeat the requested operation so that it looks like it could do something else altogether.
  • When INSTEAD OF triggers fire, SQL Server hasn’t yet made any changes and, consequently, hasn’t logged any changes.
  • INSTEAD OF trigger also don’t report any error warning because it works although the operation doesn’t go through.
  • This will be more clearer to you, if you this example:

We have used Students1 table in the following query;

1_SQL_Server_INSTEAD_OF_and_AFTER_Triggers

In the following query, the InsteadOfStud trigger causes the INSERT operation to disappear;

USE TEST
CREATE TRIGGER InsteadOfStud
ON Students1
INSTEAD OF INSERT
AS
SELECT * FROM Students1

Now, if we want to insert some values into the Students1 table, then the query would be:

INSERT Students1(SID,Name,City,State)
VALUES (5,'Neha', 'Noida', 'Uttar Pradesh');

The result can be seen as:

2_SQL_Server_INSTEAD_OF_and_AFTER_Triggers

Before moving ahead, it would be a good practice if you remove the trigger which was created above. Since leaving the trigger might create a problem;

DROP TRIGGER InsteadOfStud

Since INSTEAD OF trigger prevents the INSERT operation, hence resulting in the same table which was mentioned in the SQL statement.

   

AFTER Trigger:

  • AFTER triggers are often used for complex data validation.
  • These triggers can rollback, or undo, the insert, update, or delete if the code inside the trigger doesn’t like the operation.
  • The code can also do something else or even fail the transaction.
  • But if the trigger doesn’t explicitly ROLLBACK the transaction, the data modification operation will go as it was originally intended.
  • AFTER triggers report an error code if an operation is rolled back.
  • AFTER trigger takes place after the modification but before the implicit commit, so the transaction is still open when the AFTER trigger is fired, that is what the main advantage of using AFTER trigger.
  • So if we want to redo all the transactions then we can use the ROLLBACK keyword for all the pending transactions.
  • The following will make you understand:

This query creates the AfterStud AFTER trigger on the Students1 table, which includes the RAISERROR and ROLLBACK TRANSACTION commands:

USE TEST;
GO
CREATE TRIGGER AfterStud
ON Students1
AFTER INSERT
AS
PRINT 'After Trigger'
RAISERROR('Error',16,1);
ROLLBACK TRAN;

Now, if we want to insert some values into the Students1 table, then the query would be:

INSERT Students1(SID,Name,City,State)
VALUES (5,'Neha', 'Noida', 'Uttar Pradesh');

The result can be seen as:

3_SQL_Server_INSTEAD_OF_and_AFTER_Triggers

Before moving ahead, it would be a good practice if you remove the trigger which was created above. Since leaving the trigger might create a problem;

DROP TRIGGER AfterStud

Hence this was all about INSTEAD OF trigger and AFTER trigger.

Hope you got it understood well 🙂

And also comments on this!!

 

Regards

Piyush Bajaj

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook

   

About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

7 Comments on “INSTEAD OF and AFTER Triggers in SQL Server”

  1. Nice article, but, correct me if I’m wrong, if you raise an error in an AFTER trigger using THROW, there is no need to issue an explicit ROLLBACK because the DB does it for you. If you use the RAISERROR command with a severity level between 20 and 25, this cause the connection to be terminated and any open transaction to be rolledback.

  2. I have a question concerning this snippet:

    USE TEST;
    GO
    CREATE TRIGGER AfterStud
    ON Students1
    AFTER INSERT
    AS
    PRINT ‘After Trigger’
    RAISERROR(‘Error’,16,1);
    ROLLBACK TRAN;

    Isn’t it so, that in this case ROLLBACK is not necessery, because RAISERROR automatically rollbacks transaction?

  3. One not obvious limitation of the INSTEAD OF trigger is that you can’t intercept a statement that is trying to insert a char value into an INT column and use the INSTEAD OF trigger to substitute in an int. This is because the initial SQL statement is pre-validated and must pass CHECK constraints. If it violates any of these then the trigger gets rolled back before it can switch the value to one that won’t violate the constraint.

  4. The article discussed the details on triggers. However the article gave the wrong concept.

    Actually, INSTEAD OF trigger NOT causes the INSERT, UPDATE, or, DELETE operation to be cancelled.

    INSTEAF OF trigger does not perform the actual modification until after the trigger completes, so you do not need to undo the modification.

    On AFTER trigger case, any modification that were rejected would need to be rolled back because they have already been written to the transaction log by the time the AFTER trigger fires.

    So INSTEAD OF trigger can do INSERT, UPDATE, or, DELETE. It depends how you use it.

Leave a Reply

Your email address will not be published.