CODEWITHSUNDEEP
mysqltriggers
user3428850
user3428850

Reputation: 31

mysql error 1442 trigger

I have the following trigger:

CREATE DEFINER=root@localhost TRIGGER after_insert_student AFTER INSERT ON 

students FOR EACH ROW BEGIN 

   SET @cateID := NEW.ID ;

   IF @cateID IS NOT NULL THEN

SELECT right(max(id), 3) INTO @firstid FROM students LIMIT 1;

         SET @IDFOR = @firstid+1;

SET @DATEID = DATE_FORMAT(NOW(),'%y%d%m');

INSERT INTO students (CONCAT(@DATEID, @IDFOR), DATENEW) 

         VALUES(@IDFOR, NOW() );

   END IF;

END

ERROR:

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

Upvotes: 2

Views: 277

Answers (3)

Nabeel Ahmed
Nabeel Ahmed

Reputation: 19282

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 'MyTable' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

If all you need is to set the fields value based on previous entries from the same table or other (using SELECT). You can use BEFORE INSERT ON students trigger, this way you can set/update/edit the field values using the NEW operator.

Upvotes: 1

Shadow
Shadow

Reputation: 34284

The error message says all: your trigger handles before insert events on the students table and you attempt to insert a new record into the students table from the trigger. Which would set off this very trigger again, which would insert another row into the same table... Shall I go on?

Change it to an after insert trigger or using the NEW.fieldname=... syntax you can modify the inserted values.

Upvotes: 0

Paul Stanley
Paul Stanley

Reputation: 4098

Is it because you are actually doing the insert on a before insert trigger?

Wouldn't you leave the insert up to the event that fired it?

You'd be inserting another student based on the student you have just inserted in the same table

Try rewriting with an AFTER INSERT ON, and update the record you have just created instead.

Upvotes: 0

Related Questions