Triggers

[Skeptical]

Do triggers support transactions?

For example:

• In my PHP code I start a transaction which deletes a userID.
• This userID deletion leds to a cascade of records (from other records that point to the userID as foreign keys).
• In one of the cascaded record deletions, a trigger is called, which copies some info into another table.

If something along the way fails, would everything get rolled back, including the sql commands in the trigger?

Also, I’ve heard some people say that triggers should be avoided. Why is that?

[Peter]

Yes triggers support transactions. Modifications done by trigger are part of the current implicit or explicit transactions.

Triggers make things less transparent may be this is that is why someone tells they should be avoided. But you better ask those which say so.

It is also new functionality in MySQL 5.0 which was buggy but should be getting better.

[Skeptical]

[B]Peter wrote on Wed, 20 December 2006 22:53[/B]

Yes triggers support transactions. Modifications done by trigger are part of the current implicit or explicit transactions.

I can’t seem to find any documentation on this to confirm what you are saying. Could you provide a link perhaps?

[karlozf14]

This might be a good link

http://dev.mysql.com/doc/refman/5.1/en/using-triggers.html

"For transactional tables, failure of a statement should cause rollback of all changes performed by the statement. Failure of a trigger causes the statement to fail, so trigger failure also causes rollback. For non-transactional tables, such rollback cannot be done, so although the statement fails, any changes performed prior to the point of the error remain in effect. "