0

problem descriptions

I know that sentences in a trigger should never invoke the trigger itself, but i think i didn't while error still occurred in this case (i'll write below). So I'm really puzzled and could anyone help to correct me.

I'm using MySQL 8.0

what i encountered

Here is my tables in the database:

CodeName Password Permission Class Region Race Avatar Mail
123 456 1 NULL NULL NULL NULL NULL
Amiya amiya 1 Caster Rim Billiton Cautus NULL NULL
Doctor bbtdel 1 NULL Unknown Unknown NULL NULL
hhh 1234 2 NULL NULL NULL NULL NULL
P.R.T.S prts 0 NULL NULL NULL NULL NULL
qwert dsfrg 2 NULL NULL NULL NULL NULL
test test 2 NULL NULL NULL NULL NULL

I tried to create a trigger that will be invoked before inserting a record. if the new record's CodeName matches anyone that has already existed in the table, then i will delete the matched old one before this insert

Here's how i create my trigger:

create trigger trig_when_insert
before insert on user_account
for each row
begin
    call del_by_codename(NEW.CodeName);
end

del_by_codename() was a store procedure that i created for practice (in fact this trigger is created for practice as well and it seems stupid).

Here's how i create the procedure:

create procedure del_by_codename(in tar_codename char(30))
begin
    delete from user_account
    where CodeName = tar_codename;
end

after the trigger was build, any insert operation to this table will arouse the error:

ERROR 1442 (HY000): Can't update table 'user_account' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Is 'delete' part of the 'insert'? Really puzzled

lemon
  • 14,875
  • 6
  • 18
  • 38
Xlucidator
  • 41
  • 5
  • 2
    True and there is no way round it..Consider putting delete and insert in a transaction and/or INSERT..ON DUPLICATE KEY – P.Salmon Oct 15 '22 at 12:48
  • 1
    "Is 'delete' part of the 'insert'?" - No, `DELETE` is part of 'update' in "Can't update table". That means you can not do **any** changes to that table in your trigger, which includes `UPDATE`, `INSERT` and `DELETE`. For a solution - see the comment above. – Paul Spiegel Oct 15 '22 at 18:24

0 Answers0