LazyLJ

[rspeed]

MySQL 5

Making an UPDATE BEFORE trigger.

I want to modify a row in the same table, but not the one that triggered the trigger. Performing an UPDATE query throws an error about not allowing that behavior (updating rows in the same table). Looked it up online and apparently MySQL locks the table, but for some reason doesn't hand that lock to the

Comments

[hyperiate]

I'm not up on the latest stored procedure kung fu, but can you make your write to a secondary table, pause, then write back to the original?

Of course, if the lock doesn't expire until the procedure is complete than that's stupid.

Can you trigger the write to the secondary table from the primary, then trigger the write to the primary from the secondary using a new trigger on the secondary table?

tb

[rspeed]

Haha. It's funny because I considered that, but then realized that it was absurdly overcomplicated. So yeah, fuck it. I'll handle it in PHP.

Ah well.

[whowantscookies]

What about crafting a temporary table to do this data swap? I have found triggers to be frustrating in mySQL (though I have had little use for them sadly).

[hyperiate]

Hey, that's a good idea! I wish I had it 5 minutes or so before.... :-)

Anyway, I'd really avoid the approach (or do additional validation) in a high-transaction system because it violates about half of the ACID properties...

tb

[rspeed]

See above. :)