UPDATE行ON UPDATE触发器，多表条件 [英] UPDATE row ON UPDATE trigger, multi table condition

问题描述

我正在与Shopware合作，有一件奇怪的事情是让我疯狂:(所以我先解释一下问题是什么。

除了正常的文章，是与XS，S，M，L和/或不同颜色的相同文章...但是有不同的ordernumbers和可能不同的价格。

所以在Shopware中有一个包含核心文章数据的表，一个包含详细数据，一个包含可能的价格数据。

有多个变体在articles表中设置了一个configurator_set_id字段和一个main_detail_id字段我的问题是，我们有一个商店有很多这些变体文章和谁建立商店逻辑...可能是醉了如果主要文章的变体文章是无效的（缺货，停用等）整个变体的文章是不可买到的...没有回退到下一个变种，它仍然在商店，但你不能做任何事情。

所以我认为很容易，只是更新文章，如果变化状态改变，但那是我的问题真正开始。所以这里是表结构的第一个：

s_articles：

  + ---- + -------- + ---------------- + ------------------ --- + 
 | id |活动| main_detail_id | configurator_set_id | 
 + ---- + -------- + ---------------- + -------------- ------- + 
  

s_articles_details：

  + ------ + --------- + ----------- + 
 | id |活动| articleID | 
 + ------ + --------- + ----------- + 
  

s_articles_prices

  + ---- + ----- + ----------------- + ------- + 
 | id | articleID | articledetailID |价格| 
 + ---- + ----------- + ----------------- + ------- + $ b为了构建这篇文章，Shopware基本上从s_articles获取数据，将它们与来自s_articles_details的数据进行联接，其中id = 0。  
 
 
我尝试achive的是以下内容：
 
 
 
 
 
 
 
检查s_articles_details是否与s_articles表中的main_detail_id相对应
 
 
如果s_articles_details已更新，如果更新此行，请设置main_detail_id =（s_articles_prices.articledetailID WHERE s_articles_prices.articleID = id AND min（s_articles_prices.price））
 
 
 *我知道这不会工作，但希望它描述了什么。
 
 
 
 Quirks：
 
 
 
 
 
可能有来自s_articles_prices db 
 
 
的多个匹配项，它必须是通过s_articles_details表确定的有效变体/价格
 
 
 
 
 
任何帮助或推进正确的方向将受到高度赞赏（而我试图不碰我的头靠在墙上...）
 
 
 
 EDIT：最终解决方案
 
 
 
 
  DELIMITER $$ 
 CREATE TRIGGER update_maindetailid_after_update更新后的s_articles_details 
每个ROW 
 BEGIN 
 
 DECLARE ArticleDetailID INT（11）; 
 
 SELECT p.articledetailsID 
 INTO ArticleDetailID 
从s_articles_prices p，s_articles_details d 
 WHERE p.articleID = NEW.articleID 
 AND d.id = p .articledetailsID 
和d.active 
 ORDER BY p.price，d.ordernumber ASC 
 LIMIT 1; 
 
如果ArticleDetailID<> THEN 
 UPDATE s_articles SET s_articles.main_detail_id = ArticleDetailID 
 WHERE s_articles.id = NEW.articleID 
 AND s_articles.main_detail_id<>文章： 
 END IF; 
 
 END $$; 
 DELIMITER; 
  
 
 
解决方案
 
 
 
 <$ c $  c>  $    code> 
 
 
使用触发器正文中的 NEW 关键字引用新值。
 
 
根据新值，执行您的条件，并在其他表中更新/插入正确的值。
 
 
 
 
 
 
 
 
 帮助您开始使用的代码段 
  DELIMITER $$ 
 CREATE TRIGGER tr_au_s_articles_details更新后的s_articles_details 
每个行
 BEGIN 
  - 在这里您可以访问的id更新的文章New.id 
  -  update / insert取决于newvalue的条件。 
  - 检查id是否对应于s_articles表中的main_detail_id 
  - 这里是一个int：select count（id）from s_articles where main_detail_id = new.id 
 END; 
 DELIMITER; 
  
 
 
 
 
 
 
请参阅文档了解有关触发器语法的更多信息。
 
 
 
我不会勺子/喂食代码，但我想你应该能够轻松解决这个问题：）
 
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:


If s_articles_details is updated, get the id of the row that got updated.
Check if the id corresponds to a main_detail_id in the s_articles table
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 ;

解决方案

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 :).

                        
这篇关于UPDATE行ON UPDATE触发器，多表条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！