MySQL Deleting Row in an After Insert Trigger Error

Question

I have three tables in a MySQL database, bids, offers and matched. The matched table contains bids and offers which have been matched. There is an event that runs the logic to do the matching shown below which works.

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;

The problem occurs when I create a trigger on the matched table to delete the bid and offer inserted from the bids and offers table. I want to delete them as they are no longer needed once they are matched. The logic for this trigger is shown below.

/*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 ;

The error now occurs however when trying to execute the event to match bids and offers

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.

I am only updating the Bids table in the event match_insert_trigger but the bids table is not used to trigger anything so bit unsure what is wrong here?

EDIT

I have achieved the functionality I need by placing the logic to delete the matched bids/offers in the event bid_offer_matching. However I still do not understand how the above error is occurring if anyone knows.

New event code below

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 ;

Answer 1

The code in your event body does an INSERT INTO Matched... SELECT ... FROM Bids...

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

A stored function or trigger cannot modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.

Notice it says "for reading or writing." This means that by reading from the Bids table as you are doing in your INSERT...SELECT statement, this conflicts with the rule that you can't modify tables in the trigger that are being read from in the statement that invoked the trigger.