0

I have created a before insert trigger when new value insert as it's item_sku already exits then delete already exit line and then insert new line. but it shows error: Error Code: 1442 Can't update table 'order_item_temp' in stored function/trigger because it is already used by statement which invoked this stored function/trigger Trigger Code is

    TRIGGER LINE_DEL AFTER INSERT ON order_item_temp 
    FOR EACH ROW 
    BEGIN
    
    DECLARE ITEM_D VARCHAR(50);

     SELECT item_sku INTO @ITEM_D
        FROM order_item_temp
         GROUP BY  item_sku,shopify_order_id
        HAVING  COUNT(item_sku) > 1;

     DELETE FROM order_item_temp WHERE item_sku=@item_d;
     
    END;
$$

DELIMITER ;

and by manual entry entering new duplicate line syntax is:

INSERT INTO ehad_db.order_item_temp ( id, item_sku, item_uid, item_sid, item_qty, document_item_sid, document_item_row_version, ref_order_item_sid, ref_order_item_row_version, item_discount_amt, item_discount_code, item_discount_type, item_discount_target, shopify_order_id, order_document_sid, ref_order_sid ) VALUES ( '89', '1000000009574', '517662213000115019', '32000000306430', '1', '582945547000174220', '1', ' ', ' ', '0', ' ', ' ', ' ', '1903931129956', '582945544000151211', ' ');

now it shows above error.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
Khuram Shehzad
  • 33
  • 5
  • User defined variables are not the same thing and are not interchangeable with declared variables https://stackoverflow.com/questions/11754781/how-to-declare-a-variable-in-mysql and you cannot store a result set in a variable and I would expect to see reference to NEW. values in an after insert trigger and you cannot action the table that fired the trigger in the trigger.(as the error says). – P.Salmon Jan 04 '21 at 08:23
  • 1
    Consider Insert..on..duplicate key or REPLACE – P.Salmon Jan 04 '21 at 08:27
  • In this naive case that might work but any logic other than it existing would fail. – joshstrike Jan 04 '21 at 08:30

2 Answers2

3

It seems you can't delete rows from order_item_temp in your trigger. According to the documentation:

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.

Instead, you should handle it in your code using transaction like this

start_transaction();
delete_existed_rows();
insert_new_row();
commit();

or you could create a stored procedure that deletes already exited rows and inserts the new one, all in a transaction.

Here is an example code for creating a stored procedure

CREATE PROCEDURE insert_new_row(...)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SIGNAL SQLSTATE 'ERROR'
        SET MESSAGE_TEXT = 'INSERT FAILED';
    END;

    START TRANSACTION;
    DECLARE ITEM_D VARCHAR(50);

    SELECT item_sku INTO @ITEM_D
    FROM order_item_temp
    GROUP BY  item_sku,shopify_order_id
    HAVING  COUNT(item_sku) > 1
    FOR UPDATE;

    DELETE FROM order_item_temp WHERE item_sku=@item_d;

    INSERT INTO order_item_temp (...) VALUE (...);
    COMMIT;
END;
Minh Vương Phan
  • 151
  • 3
1

You cannot put a trigger on order_item_temp that will SELECT/DELETE from the same table order_item_temp. Triggers cannot be used to update/delete the table that triggers them. Triggers should only be used to update other tables, not the one which is triggering the action.

What you are trying to do needs to be written in your coding language, so after you get a positive result for insert, you run the delete SQL.

joshstrike
  • 1,753
  • 11
  • 15