时间戳

首页 > 解决方案 > MySQL在插入后触发错误中删除行

mysql - MySQL在插入后触发错误中删除行

问题描述

我在 MySQL 数据库中有三个表bids，offers和matched. 该matched表包含已匹配的出价和出价。有一个事件运行逻辑来进行如下所示的匹配，该事件有效。

CREATE EVENT bid_offer_matching
ON SCHEDULE EVERY 1 SECOND  
STARTS CURRENT_TIMESTAMP
DO
    INSERT INTO Matched(
        consumer_id,        /* Bidder */
        producer_id,        /* Offer */
        bid_id,             /* bid id */
        offer_id,           /* offer id */ 
        volume,             /* Volume */
        price,              /* Price */
        market_time)        /* Market Time */
    SELECT  
        bids.user_id,               /* Bidder */
        offers.user_id,             /* Offer */
        bids.bid_id,                /* Bid ID */
        offers.offer_id,            /* Offer ID */
        bids.bid_volume,            /* Volume */
        bids.bid_price,             /* Price */
        bids.market_time            /* Market Time */
    FROM  
        Bids bids
    INNER JOIN (
        SELECT *, ROW_NUMBER() 
            OVER (
                PARTITION BY 
                    offer_volume /* Partition by the volume in the offer */
                ORDER BY 
                    /* Order each partiton by the price low to high, this makes row 1 in each partition the lowest price offer*/ 
                    offer_price 
            )rn /* Get the row number also back */ 
        FROM Offers
    ) offers
    ON
        /* Volume must be equal */ 
        bids.bid_volume = offers.offer_volume AND
        /*Price must be at least asking price */ 
        bids.bid_price >= offers.offer_volume AND
        /* Market time must be same */ 
        bids.market_time >= offers.market_time
    WHERE
        /* This is important, it matches only the above condition to the lowest offer price resulting in best price for bidder */
        offers.rn = 1;

当我在表上创建触发器以删除从and表中matched插入的出价和出价时，就会出现问题。我想删除它们，因为一旦它们匹配就不再需要它们。此触发器的逻辑如下所示。bidsoffers

/*Crete the trigger*/
DELIMITER $$
CREATE TRIGGER match_insert_trigger
AFTER INSERT
ON Matched FOR EACH ROW 
BEGIN 
    /* DELETE THE BID*/
    DELETE FROM 
        Bids
    WHERE 
        bid_id=NEW.bid_id; /* The bid ID is inserted into the matched table to be used as reference, not a foreign key though */

    /* DELETE THE OFFER */
    DELETE FROM 
        Offers
    WHERE 
        offer_id=NEW.offer_id; /* The offer ID is inserted into the matched table to be used as reference, not a foreign key though */
END$$

DELIMITER ;

但是，当尝试执行事件以匹配出价和出价时，现在会发生错误

Error Code: 1442. Can't update table 'Bids' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

我只是Bids在事件中更新表格，match_insert_trigger但投标表格不用于触发任何事情，所以有点不确定这里有什么问题？

编辑

通过在事件中放置删除匹配的出价/出价的逻辑，我已经实现了我需要的功能bid_offer_matching。但是，如果有人知道，我仍然不明白上述错误是如何发生的。

下面是新的事件代码

DELIMITER $$
CREATE EVENT bid_offer_matching
ON SCHEDULE EVERY 1 SECOND  
STARTS CURRENT_TIMESTAMP
DO
BEGIN
    INSERT INTO Matched(
        consumer_id,        /* Bidder */
        producer_id,        /* Offer */
        bid_id,             /* bid id */
        offer_id,           /* offer id */ 
        volume,             /* Volume */
        price,              /* Price */
        market_time)        /* Market Time */
    SELECT  
        bids.user_id,               /* Bidder */
        offers.user_id,             /* Offer */
        bids.bid_id,                /* Bid ID */
        offers.offer_id,            /* Offer ID */
        bids.bid_volume,            /* Volume */
        bids.bid_price,             /* Price */
        bids.market_time            /* Market Time */
    FROM  
        Bids bids
    INNER JOIN (
        SELECT *, ROW_NUMBER() 
            OVER (
                PARTITION BY 
                    offer_volume /* Partition by the volume in the offer */
                ORDER BY 
                    /* Order each partiton by the price low to high, this makes row 1 in each partition the lowest price offer*/ 
                    offer_price 
            )rn /* Get the row number also back */ 
        FROM Offers
    ) offers
    ON
        /* Volume must be equal */ 
        bids.bid_volume = offers.offer_volume AND
        /*Price must be at least asking price */ 
        bids.bid_price >= offers.offer_volume AND
        /* Market time must be same */ 
        bids.market_time >= offers.market_time
    WHERE
        /* This is important, it matches only the above condition to the lowest offer price resulting in best price for bidder */
        offers.rn = 1;

/* *****DELETE STATEMENT ***** */
    /* DELETE THE OFFERS */
    DELETE FROM  
        Offers 
    WHERE 
        offer_id  
    IN 
        (SELECT offer_id FROM Matched);
        
    /* DELETE THE BIDS */
    DELETE FROM  
        Bids 
    WHERE 
        bid_id  
    IN 
        (SELECT bid_id FROM Matched);

END$$
        
DELIMITER ;

标签： mysqlsqltriggersmysql-eventmysql-error-1442

解决方案

事件正文中的代码执行INSERT INTO Matched... SELECT ... FROM Bids...

https://dev.mysql.com/doc/refman/8.0/en/stored-program-restrictions.html说：

存储的函数或触发器不能修改已被调用函数或触发器的语句使用（用于读取或写入）的表。

请注意，它说的是“用于阅读或写作”。这意味着，通过在语句中读取Bids 表INSERT...SELECT，这与您不能修改触发器中的表的规则相冲突，这些表在调用触发器的语句中被读取。

推荐阅读