CODEWITHSUNDEEP
mysqltriggers
user2677125
user2677125

Reputation: 117

MYSQL Trigger to insert column value in same table

I have a mysql Innodb table 'classrooms_subjects' as 

id|classroom_id|subject_id

classroom_id & subject_id are composite keys. Whenever i insert a row with classroom_id & subject_id, my id field is inserted as 0.

Now i want to create a trigger which will enter id field as last_inserted_id()+1.

Also I need to take care of multiple records inserted at a time. My trigger is like below:

CREATE TRIGGER `increment_id` AFTER INSERT ON `classrooms_subjects`
 FOR EACH ROW BEGIN
UPDATE classrooms_subjects
SET classrooms_subjects.id = LAST_INSERT_ID() + 1 WHERE id=0;
END

when i am inserting a record I am getting the error as: "Cant update table in trigger because it is already used by statement which invoked this trigger

Upvotes: 0

Views: 3102

Answers (1)

Peter M.
Peter M.

Reputation: 713

For general info: using an update statement inside the trigger isn't right.

Better to use a before insert trigger and simply assign the value of your column using NEW.id

http://dev.mysql.com/doc/refman/5.7/en/trigger-syntax.html

A column named with OLD is read only. You can refer to it (if you have the SELECT privilege), but not modify it. You can refer to a column named with NEW if you have the SELECT privilege for it. In a BEFORE trigger, you can also change its value with SET NEW.col_name = value if you have the UPDATE privilege for it. This means you can use a trigger to modify the values to be inserted into a new row or used to update a row. (Such a SET statement has no effect in an AFTER trigger because the row change will have already occurred.)

You should probably structure your table to make the auto_increment work properly. Better a solution that works when multiple sessions are inserting to the DB at once.

Upvotes: 1

Related Questions