After and Instead Of Triggers

After Triggers

“After triggers” are triggers which get fired after executing SQL statement. After triggers are also knows as “For triggers” or simple triggers. In the old version of SQL server (prior to version 2000), these are the only triggers available. After trigger fires after Insert, Update and Delete actions.

To understand After trigger in the real world, lets create a dummy table first on which a After trigger will be created.

To create a table, open Microsoft SQL Server Management Studio and click on the menu File->New->Query

Note: You will have to select a database in which you want table created.

Copy paste or type below SQL statement in query window and run the SQL.

Create table Mytable

( col1 int

)

Above Statement will create Mytable named table in the selected database.

Now let’s create After update trigger:

Create TRIGGER Mytable_trigger on Mytable After Update

AS

Begin

Select * from Mytable;

Select * from inserted;

Select * from deleted;

Select '------------------------';

End

Above trigger is using “inserted” and” deleted” logical tables. This is the advantage we got in trigger. We can see new value updated or inserted in “inserted” logical table and old values which got replaced by new values in “deleted” logical table.

Now insert few rows into Mytable so that we can modify them and see how trigger react to them.

insert into Mytable values(1);

insert into Mytable values(2);

insert into Mytable values(3);

insert into Mytable values(4);

insert into Mytable values(5);

Now fire the following update statement in query window

Update Mytable set col1=col1 * 2

After firing the above SQL statement you will see following result in the result panel

Observe gthat Mytable values got updated already when trigger got fired.

As there can be multiple after trigger on a table, to set order of triggers, there is sp_settriggerorder standard stored procedure available.

Conclusion:

From above steps it is proved that After triggers are fired after Insert /Update/ Delete actions.

Instead Of Triggers

After triggers fire after update/insert/delete action whereas Instead Of triggers fire in place of insert/update/delete action.

There can be only one Instead of trigger of a type on a table.

To see how Instead If triggers works,

Change above created table using following SQL statements:

ALTER TRIGGER [dbo].[Mytable_trigger] on [dbo].[Mytable] Instead Of Update

AS

Begin

Select * from Mytable;

print 'Not allowing to update values'

Rollback;

End

Now reset Mytable values by firing following statements:

delete from Mytable;

insert into Mytable values(1);

insert into Mytable values(2);

insert into Mytable values(3);

insert into Mytable values(4);

insert into Mytable values(5);

Now ready to see what happens if we update the values of Mytable. Fire following update statement and notice the result

Update Mytable set col1=col1 * 2

Conclusion:

Result shows value of Mytable which did not update . So instead of trigger give us option to implement other logic instead of actual action. We can still get updated values in inserted table in, in case we have to validate updated values and then commit those.

Happy Triggering!
Sanjivani Kumar