Error Code: 1442. Can't update table 'students' in stored function/trigger

Question

I have the following trigger:

CREATE DEFINER=root@localhost TRIGGER after_insert_student after INSERT ON 
students FOR EACH ROW BEGIN 
    SET @NEWID := NEW.ID ;
if @NEWID IS NOT NULL THEN     
     INSERT INTO students SET ID = @NEWID;
else
     INSERT INTO students SET ID = 001;
END IF
END

ERROR:

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

Answer 1

You cannot change a table while the INSERT trigger is firing. The INSERT might do some locking which could result in a deadlock. Also, updating the table from a trigger would then cause the same trigger to fire again in an infinite recursive loop. Both of these reasons are why MySQL prevents you from doing this.

To do this, please reference this link

Answer 2

You probably want a BEFORE INSERT trigger. Then instead of updating the table, just assign the desired value to NEW.ID.

Answer 3

The problem is that you can't write statement like

INSERT INTO students SET ID = @NEWID;

because it requires to know which columns you want to insert, or if not columns specified then you insert into all columns values etc.

INSERT INTO students values (@NEWID);

should work or if you want to stick to your SET ID then try to write something like

UPDATE students SET ID = @NEWID;

Hope this guides you to solution to your problem.

Answer 4

Solution: make it a BEFORE UPDATE ON trigger, and use NEW operator, it'll do the job - as all you want is if new ID is NULL set it to 001:

CREATE DEFINER=root@localhost TRIGGER after_insert_student 
BEFORE INSERT ON students 
FOR EACH ROW BEGIN 
    IF NEW.ID IS NULL THEN     
        SET ID = 001;
    END IF;
END;

---

Cause: You cannot update a table (students) where the trigger is invoked:

Within a stored function or trigger, it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.

Doing so will generate Error 1442:

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