Difference Between Insert / After Insert, Update / After Update, etc. Triggers

Question

Post reply

Difference Between Insert / After Insert, Update / After Update, etc. Triggers

david.c.holley

SSCrazy

Points: 2423
More actions
July 19, 2010 at 1:37 pm

#235508

What are the finer nuances between INSERT / AFTER INSERT and its cousins (UPDATE & DELETE)?
How does firing the trigger as apart of the action verses after the action is complete effect processing?
I'm building a trigger that to manage records in a related, child table and would like to get a better idea of issues to be considered.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

None whatsoever. When it comes to triggers, FOR and AFTER are synonyms.

CREATE TRIGGER ...

FOR INSERT

means exactly the same as

CREATE TRIGGER ...

AFTER INSERT

The trigger runs after the firing action, within the same transaction.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Jones - SSC Editor SSC Guru Points: 736252 More actions · Answer 2

If you modify a table, the trigger runs after the modification is done, but before it is committed.

david.c.holley SSCrazy Points: 2423 More actions · Answer 3

Steve Jones - Editor (7/19/2010)
If you modify a table, the trigger runs after the modification is done, but before it is committed.

So you're saying that the big thing to consider is what's going on in the trigger in terms of table structure? If you're not changing any structure, then there's no functional difference.

Steve Jones - SSC Editor SSC Guru Points: 736252 More actions · Answer 4

Sorry, no, modify the data in a table.

If I have Customers:

Create table Customers( Customer varchar(20))

go

insert Customers select 'Acme'

union select 'Brad'

go

Now I want to change the Customer name to "Smith" and have the old value logged in another table.

When I update,

update Customers set Customer = 'Smith'

if I have a trigger on this table, the update will take place, and the inserted table will have 2 rows of 'Smith' and deleted will have 2 rows (1 Acme, 1 Brad). If the trigger inserts the old values into some other table, before any users other than the one making the update, can see the changes, both the update to the table's data AND the trigger have to complete. They are bound inside the same transaction.

david.c.holley SSCrazy Points: 2423 More actions · Answer 5

Steve Jones - Editor (7/19/2010)
Sorry, no, modify the data in a table.
If I have Customers:
Create table Customers( Customer varchar(20))
go
insert Customers select 'Acme'
union select 'Brad'
go
Now I want to change the Customer name to "Smith" and have the old value logged in another table.
When I update,
update Customers set Customer = 'Smith'
if I have a trigger on this table, the update will take place, and the inserted table will have 2 rows of 'Smith' and deleted will have 2 rows (1 Acme, 1 Brad). If the trigger inserts the old values into some other table, before any users other than the one making the update, can see the changes, both the update to the table's data AND the trigger have to complete. They are bound inside the same transaction.

When your working with a trigger that first on Update as opposed to After Update? So it comes down to looking at the values being changed throughout the database and whether or not any additional SQL needs to see the changes or not?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

david.c.holley (7/19/2010)
When your working with a trigger that first on Update as opposed to After Update?

Not quite sure what you mean, but there's only two types of DML triggers in SQL. The Instead Of trigger and the After trigger (For is synonymous with After here). There's no BEFORE trigger.

Instead of triggers fire instead of the actual operation. So an Instead Of Update trigger will fire in place of the update (the update won't happen)

After triggers fire after the modifications have been made to the table but before the transaction is considered complete. Hence a rollback in the trigger rolls back the triggering operation.

So it comes down to looking at the values being changed throughout the database and whether or not any additional SQL needs to see the changes or not?

Not clear what you're getting at.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Jones - SSC Editor SSC Guru Points: 736252 More actions · Answer 7

Gail has given a great explanation. There is no BEFORE trigger. The create trigger...for update is the same as the AFTER syntax.