UPDATE row ON UPDATE trigger, multi table conditio

Question

I'm working with Shopware and one odd thing is driving me nuts :( So I will first explain what the Problem is.

Aside from normal articles, there are those with multiple variants, like a shirt in different sizes. It's the same article in XS, S, M, L and/or different colors... but has different ordernumbers and probably different prices.

So in Shopware you have a table with the core article data, one with the details data and one with the possible prices data.

The articles with multiple variants have a configurator_set_id field and a main_detail_id field set in the articles table. My problem is, that we have a shop with a lot of those variant articles and whoever build the shop logic... probably was drunk. If the main article of the variant article is inactive (out of stock, deactivated and so on) the whole variant article isn't buyable anymore... there is no fallback to the next variant, its still in the shop but you cant do anything at all.

So I thought easy going, just update the article if the variants status change... but thats where my problems really start. so here is the table structure at first:

s_articles:

+----+--------+----------------+---------------------+  
| id | active | main_detail_id | configurator_set_id |  
+----+--------+----------------+---------------------+  

s_articles_details:

+------+---------+-----------+
|  id  | active  | articleID |
+------+---------+-----------+

s_articles_prices

+----+-----------+-----------------+-------+
| id | articleID | articledetailID | price |
+----+-----------+-----------------+-------+

To build the article, Shopware basically takes the data from s_articles, joins them with the data from s_articles_details where id = s_articles.main_details_id.

What I was trying to achive is the following:

1. If s_articles_details is updated, get the id of the row that got updated.
2. Check if the id corresponds to a main_detail_id in the s_articles table
3. If it does update the row, set main_detail_id = (s_articles_prices.articledetailID WHERE s_articles_prices.articleID = id AND min(s_articles_prices.price))

*i know this wont work, but hopefully it describes what I ment

Quirks:

• there may be more than one match from the s_articles_prices db
• it has to be an active variant/price which is determined via the s_articles_details table

Any help or push in the right direction will be grately appreciated (while I try to not bang my head against the wall...)

EDIT: Final Solution

jean-françois-savard pointed me to the right direction, so I came up with the following solution:

DELIMITER $$
CREATE TRIGGER update_maindetailid_after_update AFTER UPDATE ON s_articles_details
FOR EACH ROW
BEGIN

    DECLARE ArticleDetailID INT(11);

    SELECT  p.articledetailsID
    INTO ArticleDetailID
    FROM s_articles_prices p, s_articles_details d
    WHERE p.articleID = NEW.articleID 
        AND d.id = p.articledetailsID
        AND d.active
    ORDER BY p.price, d.ordernumber ASC
    LIMIT 1;

    IF ArticleDetailID <> "" THEN
        UPDATE s_articles SET s_articles.main_detail_id = ArticleDetailID 
        WHERE s_articles.id = NEW.articleID
            AND s_articles.main_detail_id <> ArticleDetailID;
    END IF;

END$$;
DELIMITER ;

Answer 1

• Trigger is what you need here.
• Create a trigger that will be fired each time an update is done on s_articles_details
• Refer to the new value using the NEW keyword in the body of the trigger.
• According to the new value, do your condition, and update/insert the correct values in other tables.

---

Snippet to help you get started

DELIMITER $$
CREATE TRIGGER tr_au_s_articles_details AFTER UPDATE ON s_articles_details
FOR EACH ROW
BEGIN
   -- Here you can access the id of the updated article New.id
   -- update/insert depending on the condition of the newvalue.
   -- Check if id corresponds to a main_detail_id in the s_articles table
   -- Here an int : select count(id) from s_articles where main_detail_id = new.id
END;
DELIMITER ;

---

See documentation for more information on Trigger syntax.

I won't spoon/feed the code, but I think you should be able to easily resolve this now :).