Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I created a trigger AFTER an UPDATE of a column in a MySQL database. When the trigger detects a certain value, it should perform some actions. This is what I have at the moment and is working fine.

CREATE TRIGGER `order_gereed` AFTER UPDATE ON `oc_order`
FOR EACH ROW IF new.order_status_id = "15"
THEN
UPDATE push_voorraad SET actie = '1';
END IF

But now I need to do some more SQL when new.order_status_id matches 15. I don't know how to do this in pure SQL, but I do now how to in php. So I hope with posting my php code, someone can tell me how to do this in SQL.

$query = "SELECT
            oc_order_product.quantity,
            oc_order_option.option_value_id,
            product.id
          FROM oc_order_product
            JOIN oc_order_option
              ON oc_order_option.order_id = oc_order_product.order_id
            JOIN product
              ON product.oc_product_id = oc_order_product.model
          WHERE oc_order_product.order_id = '25' AND oc_order_product.store_url = 'http://www.someurl.com/'";

$result = $mysqli->query($query);
while($row = $result->fetch_object()){
  $query = "UPDATE product_option_value SET quantity = quantity - {$row->quantity} WHERE product_id='{$row->id}' AND option_value_id='{$row->option_value_id}'";
  $mysqli->query($query);
}

25 in the query above should actually be new.order_id.

http://www.someurl.com/ in the query above should actually be new.store_url

share|improve this question

1 Answer 1

Oke, I found my own answer after reading the internet en doing some trail and error.

I created a stored procedure which I will execute on the update trigger.

The trigger

CREATE TRIGGER `order_gereed` AFTER UPDATE ON `oc_order`
 FOR EACH ROW IF new.order_status_id = "15"
THEN
CALL VooraadUpdate(new.order_id, new.store_url);
UPDATE push_voorraad SET actie = '1';
END IF

The procedure

CREATE DEFINER=`root`@`localhost` PROCEDURE `VooraadUpdate`(IN `orderID` INT(11), IN `storeURL` VARCHAR(255))
    NO SQL
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE a, b, c INT;
    DECLARE cur1 CURSOR FOR SELECT
                              oc_order_product.quantity,
                              oc_order_option.option_value_id,
                              product.id
                            FROM oc_order_product
                              JOIN oc_order_option
                                ON oc_order_option.order_id = oc_order_product.order_id
                              JOIN product
                                ON product.oc_product_id = oc_order_product.model
                            WHERE oc_order_product.order_id = orderID AND oc_order_product.store_url = storeURL;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur1;
    read_loop: LOOP
      FETCH cur1 INTO a, b, c;
        IF done THEN
          LEAVE read_loop;
        END IF;

      UPDATE product_option_value SET quantity = quantity - a WHERE product_id=c AND option_value_id=b;

    END LOOP;

    CLOSE cur1;
END
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.