I have been pulling my hair out trying to come up with a way to update a different row in the same table as a trigger. There simply has to be a way around the circular reference problem. The following code does not create a circular reference but I don't know how to make MySQL understand that. DROP TRIGGER IF EXISTS update_master; delimiter $$ CREATE TRIGGER update_master AFTER UPDATE On product FOR EACH ROW BEGIN DECLARE qty,prod_id,vend_id,master_id INT; SET master_id=6; SELECT vendor INTO vend_id FROM vendor WHERE vproduct_id=OLD.product_id; IF ((vend_id<=>master_id)=0) THEN IF ((NEW.quantity<=>OLD.quantity)=0) THEN SELECT vendor.vproduct_id INTO prod_id FROM product JOIN vendor ON product.product_id=vendor.vproduct_id WHERE product.model=OLD.model AND vendor.vendor=master_id; SELECT quantity INTO qty FROM product WHERE product_id=prod_id; UPDATE product SET quantity=qty+NEW.quantity-OLD.quantity WHERE product_id=prod_id; END IF; END IF; END$$ delimiter ; Code (markup): The embedded update WHERE clause uses only product_ids that belong to master_id. In other words when an update is attempted on a product_id whose vend_id equals master_id the trigger exits. Thus no circular reference happens. I have read about using semaphores to circumvent this problem but none work. The crux of the problem seems to be that there is no way to run another trigger when the current trigger finishes. I tried the following: DROP TABLE IF EXISTS semaphore; CREATE TABLE semaphore ( id INT PRIMARY KEY, action VARCHAR(8), product_id INT, quantity INT ); DROP TRIGGER IF EXISTS update_master; -- DROP TRIGGER IF EXISTS delete_semaphore; DROP TRIGGER IF EXISTS semaphore; delimiter $$ CREATE TRIGGER update_master BEFORE UPDATE On product FOR EACH ROW BEGIN DECLARE qty,prod_id,vend_id,master_id,semaphore_id INT; SET master_id=6; SELECT vendor INTO vend_id FROM vendor WHERE vproduct_id=OLD.product_id; IF ((vend_id<=>master_id)=0) THEN IF ((NEW.quantity<=>OLD.quantity)=0) THEN SELECT vendor.vproduct_id INTO prod_id FROM product JOIN vendor ON product.product_id=vendor.vproduct_id WHERE product.model=OLD.model AND vendor.vendor=master_id; SELECT quantity INTO qty FROM product WHERE product_id=prod_id; SET semaphore_id = FLOOR(RAND()*1000.); SET qty = qty+NEW.quantity-OLD.quantity; INSERT INTO semaphore (id,action,product_id,quantity) VALUES (semaphore_id,'update',prod_id,qty); END IF; END IF; END$$ CREATE TRIGGER semaphore BEFORE DELETE ON semaphore FOR EACH ROW BEGIN IF (STRCMP(OLD.action,'update')=0) THEN UPDATE product SET quantity=OLD.quantity WHERE product_id=OLD.product_id; END IF; END$$ delimiter ; Code (markup): This works perfectly as long as I manually delete the semaphore from command line. However, as soon as I add this trigger: CREATE TRIGGER delete_semaphore AFTER UPDATE ON product FOR EACH ROW BEGIN DELETE FROM semaphore; END$$ Code (markup): I'm back to the circular reference problem, which of course is not really the case. Can someone help me over this hurdle? P.S. This is a 3rd party database application so I cannot change its structure without massive code changes.
Out of shear desperation, I have created an event that runs every 5 seconds to do the delete. However, there surely must be a better way.