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;

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:

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:

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