CODEWITHSUNDEEP
mysqlsql
Khuram Shehzad
Khuram Shehzad

Reputation: 33

Can't Working my Before insert trigger in mysql

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.

Upvotes: 0

Views: 568

Answers (2)

Minh Vương Phan
Minh Vương Phan

Reputation: 151

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;

Upvotes: 3

joshstrike
joshstrike

Reputation: 1823

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.

Upvotes: 1

Related Questions