CODEWITHSUNDEEP
sqltriggersdatabase-trigger
zer0uno
zer0uno

Reputation: 8030

What a BEFORE trigger can do that an AFTER trigger can't and conversely

I've read a lot about these type of trigger and what I understood until now is that they can do exactly the same things, the difference is more about "taste" than what they really are able to do.

So what I'm asking is: is there something that a BEFORE trigger can't do compared to an AFTER trigger and conversely? Am I missing something?

Upvotes: 2

Views: 86

Answers (2)

Matt
Matt

Reputation: 14341

To Expand a little on @Shnugo's answer, note my comments are more specific to sql-server but I believe principles hold true for other rdbms that have the triggers.

What can BEFORE (or instead of) do that AFTER cannot

Say you have a BEFORE trigger on a blank table with an identity column that does nothing/no insert. To get a similar result in an AFTER trigger you would have to delete the inserted records.

Let's walk through inserting records with the different triggers. If BEFORE trigger is enabled and you do 100 inserts, but trigger doesn't actually insert them, then you disable the trigger and do an insert you will be at identity 1.

Do the same thing for an AFTER trigger and when you insert after you will be at 101. Because the records where actually inserted, but then deleted.

So a BEFORE trigger can stop an action completely where as an AFTER has to try to undo an action to get a similar result in the data. Complex Validation? Or in Shnugo's example more common insert a parent record before inserting a child to that parent so a foreign key constraint error doesn't occur.

What can an AFTER trigger do that a BEFORE cannot.

Use the identity column on an insert statement. In sql-server the special table inserted in the same BEFORE trigger as above will return Identity = 0 instead of Identity = 1. Where an AFTER trigger will have Identity = 1. So in the BEFORE you could avoid a foreign key constraint by inserting a parent and in the AFTER you can do the opposite you can insert a child record with the proper foreign key.

Upvotes: 2

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

The before trigger allows you to set action before any other action is done.

Just imagine a table with a foreign key column which is not nullable. You might insert this depending row before you insert the row which needs this FK to be set.

You could prohibit any action at all (no changes allowed to key tables...)

You could check for existance and do an update instead of an insert

and many more...

Upvotes: 4

Related Questions