Unable to create a MySQL/MariaDB Stored Procedure

Question

I have a XML file which contains 200k records. I have to insert or update the records in the database. My database structure is as followed:

+----+------+-------+-----+-----+------------+
| Id | name | stock | sku | ean | updated_by |
+----+------+-------+-----+-----+------------+

For inserting the records i'm using Doctrine. But it takes to long before all records are inserted or updated. Therefore I want to create a stored procedure.

I tried the following stored procedure but it's not working.

delimiter $$
create trigger insert
before insert on Product
for each row
begin
IF(exists(select 1 From Product WHERE sku = new.sku))
THEN
    BEGIN
    UPDATE Product SET stock = new.stock WHERE sku = sku;
    END;    
END IF;
end$$
delimiter

Error:

General error: 1442 Can't update table 'Product' in stored function/trigger because it is already used by statement which invoked this stored function/trigger

Update:

Read https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html. That will work without using a trigger. — Bill Karwin, Sep 07 '19 at 20:00
@BillKarwin I'm using Doctrine ORM which is handling the database query for the insert. We are using annotation, we do not create our database by ourself. Therefore we want to handle this in MySQL with stored procedure — Noob, Sep 07 '19 at 20:02

score 2 · Answer 1 · answered Sep 07 '19 at 20:23

You can't use a MySQL trigger in the way you describe. You can't update the table for which the trigger is defined. It would result in an infinite loop.

Even if the trigger code you wrote worked, MySQL does not support "instead of" triggers. That is, it would do the UPDATE but then it would also attempt the INSERT. So it would either create another row with the same sku value, or else if you have a unique constraint on sku, the insert would cause an error and roll back both the INSERT and the UPDATE run from the trigger.

This is why the REPLACE and INSERT...ON DUPLICATE KEY UPDATE statements exist.

I understand you want to use ORM methods for all operations, but this will be a good example of a general rule: ORM's do not support all operations. Inevitably you will encounter a case where you have to write SQL if you want to do something special. Doctrine, like all ORM libraries, supports a way to run literal SQL statements, bypassing the ORM methods.

See Doctrine: ON DUPLICATE KEY UPDATE for example.

Thanks for the explanation, if I use replace or insert on duplicate key update it takes to much time before the XML with the 200k records are finished. Since it has to check and write it to the database which is an expensive query. — Noob, Sep 07 '19 at 20:27
You might like my presentation [Load Data Fast!](https://www.slideshare.net/billkarwin/load-data-fast) — Bill Karwin, Sep 07 '19 at 21:50

score 1 · Answer 2 · answered Sep 07 '19 at 20:17

1

You should use insert on duplicate

but your trigger should be li9ke this your first insert must be the trigger name and the end is missing a ;

delimiter $$
create trigger insert_prodzct
before insert on Product
for each row
begin
  IF(exists(select 1 From Product WHERE sku = new.sku))
  THEN
    BEGIN
      UPDATE Product SET stock = new.stock WHERE sku = sku;
    END;    
  END IF;
 end$$
 delimiter ;

answered Sep 07 '19 at 20:17

nbk

45,398
8
30
47

General error: 1442 Can't update table 'Product' in stored function/trigger because it is already used by statement which invoked this stored function/trigger – Noob Sep 07 '19 at 20:21

Unable to create a MySQL/MariaDB Stored Procedure

2 Answers2