Good day

There is an oc_product table with columns

product_id

quantity ,

price .

There is a map_product dependency table: it indicates how much in which product of other goods, i.e. columns

flower - id of the composite item in the oc_product table,

bunch - product id in the oc_product table that is included in this composite,

count - the amount of bunching in the flower.

Tables, in view of the device, are in different databases

I tried to quickly trigger a trigger that will change the price of a composite product when the price of a member of a composite product changes.

For example, this is a composite bouquet of 3 red roses, 5 white, 7 blue.

We changed the price for a blue rose, you need to recalculate the price of a composite bouquet.

Here is the code of the misery that I invented:

 DELIMITER $$ CREATE TRIGGER price_change after update ON `cms_flower`.oc_product BEGIN UPDATE `oc_product` SET `price` = (SELECT SUM(`summa`) FROM (SELECT `bunch` * `count` * (SELECT `price` FROM `oc_produc` WHERE `product_id` = (SELECT `bunch` FROM `flower_deliver`.map_product WHERE `flower` = NEW.product_id)) AS `summa` FROM `flower_deliver`.map_product WHERE `flower` = NEW.product_id)) WHERE `product_id` = (SELECT `bunch` FROM `flower_deliver`.map_product WHERE `flower` = NEW.product_id) END$$ DELIMITER ;

Issues:

oc_product the right oc_product

I do not know how to implement such a trigger due to my inexperience, please help, thanks in advance.

    2 answers 2

    The idea of ​​a trigger on a table update event that updates the record of the same table is in itself flawed. Not to mention the fact that there is a potency to get a cycle by hanging the server tightly, and the integrity subsystem does not catch such a dependence. I strongly recommend to refuse the trigger.

    And there is no double entry (the flower is part of the bouquet, which is part of the basket)? if yes, it’s really bad ...

    I think a much more correct solution would be to implement the update logic not in the trigger, but in the stored procedure format. There both recursion can be organized, and multi-level, and take into account that the updated product can be included in several components, and simplify your life with temporary tables.

    • Of course it's great, but how to implement the body of this procedure? At least in what direction to dig, too many subqueries, even if they are driven into view - zaki hatfild

    Here is a suboptimal, but understandable, algorithm.

    In the procedure we pass the ID (or something else identifying) of the goods being changed and the new price. We put this information in a temporary table with a unique index for the product (there are enough ID and cost fields). We will receive all the goods that depend on the goods in the temporary table by the request, we put them in the second temporary table (of the same structure and also indexed by product) by the INSERT IGNORE query. With the second query, we will get the value of the goods from the second temporary table, and put them in the first query with INSERT IGNORE. If at the same time AFFECTED ROWS is not zero - repeat the procedure. If it is zero, perform the REPLACE of the main table with data from the first temporary one. Everything.

    The only thing that can cause complexity is the second query - it will be necessary to use both the main and the first temporary table to get the price. That is, to the second time bind them both by ID (temp2 INNER JOIN main LEFT JOIN temp1) and get the price as COALESCE (temp1.price, main.price).

    • Nuuu, about understandable ... - zaki hatfild
    • there is no count of the number of goods in the composite - zaki hatfild
    • And the quantity needed only when calculating the new price. - Akina
    • Chef, nothing is clear - zaki hatfild
    • I can’t figure out how to write - zaki hatfild