How to delete a record in a trigger

Question

My trigger looks like that:

CREATE TRIGGER addATicket AFTER INSERT ON ticket FOR EACH ROW BEGIN

declare numTicket int(2) default 0;
select count(*) into numTicket from ticket
    where (ticket.ticket_id != new.ticket_id) 
    and (ticket.seat_code = new.seat_code);

if (numTicket > 0) then
    delete from ticket where ticket_id= new.ticket_id;
end if;END

But my workbench catches an error:

Error Code: 1442. Can't update table 'ticket' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

How can I solve this problem. Please give me a help.

score 0 · Answer 1 · edited May 23 '17 at 10:26

This is most probably because your main INSERT query which is firing TRIGGER holding a row level lock on that table and hence your trigger is complaining about the same.

most probably you want to change the trigger definition from AFTER INSERT ON ticket to BEFORE INSERT ON ticket (What I mean is use a BEFORE TRIGGER instead).

See a almost similar post here

MySql Error: Can't update table in stored function/trigger because it is already used by statement which invoked this stored function/trigger

How to delete a record in a trigger

1 Answers1