#1
  1. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,464
    Rep Power
    594

    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?
    Code:
    DROP TRIGGER IF EXISTS increment_master;
    delimiter $$
    CREATE TRIGGER increment_master ON product BEFORE UPDATE
    DECLARE master,qty,prod_id INT;
    master_id=6;
    BEGIN
            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
                            WHERE product.model=OLD.model
                            AND vendor.vendor=master_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
                    qty=qty+NEW.quantity-OLD.quantity;
    # update the master quantity record
                    UPDATE product SET quantity=qty WHERE  product_id=prod_id;
            END IF;
    END$$
    delimiter ;
    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.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,464
    Rep Power
    594
    After a lot of reading I am making no progress on how to circumvent the circular reference. My latest attempt was to use a stored procedure which eliminates the circular reference error but never seems to execute my procedure (perhaps for the same reason but no error is output). Here is my current code:
    Code:
    SET @master_id=6;
    DROP PROCEDURE IF EXISTS update_master;
    DROP TRIGGER IF EXISTS update_master;
    delimiter $$
    CREATE PROCEDURE update_master(IN id INT,IN qty INT)
    BEGIN
            UPDATE product SET quantity=qty WHERE product_id=id;
    END$$
    CREATE TRIGGER update_master AFTER UPDATE On product FOR EACH ROW
    BEGIN
            DECLARE qty,prod_id INT;
            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;
                    CALL update_master(prod_id,qty+NEW.quantity-OLD.quantity);
            END IF;
    END$$
    delimiter ;
    Can someone please help me over this hump? TIA.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,464
    Rep Power
    594
    Well there was bug in the above code that suppressed the circular reference (my session variable was not working right). I have it corrected but am back to the circular reference problem again. Surely there is someone out there that knows how to get around this problem?
    There are 10 kinds of people in the world. Those that understand binary and those that don't.

IMN logo majestic logo threadwatch logo seochat tools logo