Trigger to update different row in same table

Discussion in 'Databases' started by gw1500se, Jul 30, 2013.

  1. #1
    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.
     
    gw1500se, Jul 30, 2013 IP
  2. gw1500se

    gw1500se Member

    Messages:
    21
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    36
    #2
    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.
     
    gw1500se, Jul 30, 2013 IP