Tonk Tonk - 10 days ago 9
SQL Question

MySQL trigger Error: 1442

DB: MySQL

Using: MySQL Workbench

I am currently working on a project for class where we have to design a airport website in which users can login/register and buy tickets for various flights offered. After working on the project for some time I realized that my trigger is not working.

I receive the error:

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


I stumbled upon this error by trying to insert a reservation before creating the form for users to select and buy tickets. I was wondering if someone could take a look at my database, insert command use, and also my trigger code. Any help would be much appreciated because I am at wits end trying to figure this out for hours.

Insert command: http://pastebin.com/tyBPWNDd

Entire DB and Trigger in question: http://pastebin.com/BvUj1NdH

Answer

Yes, the error is self explanatory. You are trying to create a recursive trigger which is not supported in MySQL as can be seen in your posted code

Create Trigger reservation_all
    after insert on Reservation
    for each row
    begin
        update Reservation

Check MySQL Documentation on same for more infromation. Quoting from that

  • Stored functions cannot be used recursively.

  • 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.

Comments