| Triggers [message #491] |
Wed, 20 December 2006 08:49  |
Skeptical
Messages: 4
Registered: December 2006 |
Junior Member |
|
|
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?
|
|
|
| Re: Triggers [message #492 is a reply to message #491 ] |
Wed, 20 December 2006 09:53  |
 Peter
Messages: 405
Registered: August 2006 |
Senior Member
Super Guru |
|
|
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.
Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
|
|
|
| Re: Triggers [message #2436 is a reply to message #492 ] |
Sat, 29 December 2007 10:35 |
Skeptical
Messages: 4
Registered: December 2006 |
Junior Member |
|
|
| Peter wrote on Wed, 20 December 2006 22:53 |
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?
|
|
|
| Re: Triggers [message #3325 is a reply to message #492 ] |
Tue, 22 July 2008 14:19 |
karlozf14
Messages: 1
Registered: July 2008 |
Junior Member |
|
|
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. "
|
|
|
Members
Search
Help
Register
Login
Home
