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;

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

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;

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:

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

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;

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