July 27th, 2013, 08:51 AM
Trigger Sanity Check - Update Row in Same Table
This has to be a reasonably common problem but solutions are not. I need to update a different row in the same table from a trigger. I am aware of the issue with recursion and my IF statement eliminates that, I think. However, will MySQL understand that recursion will not occur (although a 2nd trigger does happen but exits) or is there a better way to accomplish this?
DROP TRIGGER IF EXISTS increment_master;
CREATE TRIGGER increment_master ON product BEFORE UPDATE
DECLARE master,qty,prod_id INT;
IF ((NEW.quantity <=> OLD.quantity)=0 AND (OLD.product_id <=> master_id)=0) THEN
# select master product_id corresponding to the updated model value
SELECT vendor.vproduct_id INTO prod_id FROM product
JOIN vendor ON product.product_id=vendor.vproduct_id
# get current quantity from the master
SELECT quantity INTO qty FROM product WHERE product_id=prod_id;
# adjust the master quantity based on the changed current record quantity
# update the master quantity record
UPDATE product SET quantity=qty WHERE product_id=prod_id;
I've seen some complicated solutions that use an additional table and semaphores but I have not been able to comprehend the sequence of events sufficiently to write my own and am not sure that level of complexity is necessary. TIA.
There are 10 kinds of people in the world. Those that understand binary and those that don't.