SQL Server: About the for triggers and the After triggers

Hi Guys,

Welcome back!

After the last post about How to read an XML file from SQL Server ,today we have only some brief notes about triggers!
We talk about the for triggers and after triggers . 
So, are you ready?

Introduction

I think we all know what is a SQL Server Trigger.
A trigger is simply a procedure triggered after the execution of a Insert, Update or Delete T-SQL Command.

When you write a trigger you could specify the option FOR / AFTER or INSTEAD OF.
What is the difference?

If you specify FOR/AFTER parameter trigger is fired when "all operations specified in the triggering SQL statement have executed successfully".

While if you specify the ISTEAD OF parameter the trigger is fired in place of the T-SQL statement.

But, what is the difference between the clause FOR and the clause AFTER?

For Triggers and After Triggers

In order to do an example let's take our famous ORDTES table.
Now define the triggers below:

CREATE TRIGGER [dbo].[TR_ORDTES_UPD_FOR] ON [dbo].[ORDTES]
   FOR UPDATE
AS
BEGIN
   Select 'TRIGGER IN FOR ....'
END

 
CREATE TRIGGER [dbo].[TR_ORDTES_UPD_AFTER] ON [dbo].[ORDTES]
   AFTER UPDATE
AS
BEGIN
   Select 'TRIGGER IN AFTER ....'
END
GO

The first trigger use the FOR clause, the second the AFTER clause.

Now let's start the SQL Server Profiler and then execute the T-SQL command:

 
UPDATE dbo.ORDTES SET TIPNUM = TIPNUM WHERE ID = 1

Can you see the difference?

1) The trigger with the FOR Clause is executed before the trigger with the AFTER clause.
 
2) If you execute a T-SQL command and it fails SQL Server will not fire the After Trigger.

Finally note that values read from inserted and deleted temporary tables are equal.



That's all for today!
See you soon and don't forget to subscribe to this blog in order to receive immediately a notification when a new post is published!
Luca


Luca Biondi @ SQLServerPerformance blog 2020!  

Previous post: How to read XML file from SQL Server. The OPENXML statement and a little bit of Datascience