Before / after insert trigger using auto increment field

Question

I'm having troubles with an insert trigger that is supposed to fix a column in a table:

id        - auto increment int
thread_id - int [NULL]

What I want to achieve is to set the thread_id to id if it's inserted as NULL. I have failed because:

• using before insert trigger still does not have the new value for id and mysteriously sets thread_id to 0
• using after insert - update trigger throws reasonable exception can't update table because it is already used by the statement.
• you can not add additional auto increment field

What is the solution to this problem?

Answer 1

The only way I found to get the new increment value is not a fancy form. I assumed that you are inserting a new value, so it is suppose to be the maximum id + 1:

CREATE TRIGGER mytrigger BEFORE INSERT ON yourtable 
FOR EACH ROW BEGIN
SET NEW.thread_id = IF(
ISNULL(NEW.thread_id), 
(SELECT MAX(id) + 1 FROM yourtable), 
NEW.thread_id);
END;

It worked for my case, if it's NULL it will get the max ID + 1, which will be the new id.

Answer 2

i had the same problem.
the code below is not working :

    `DELIMITER #
CREATE DEFINER='user'@'server' TRIGGER mytrigger BEFORE INSERT ON mytable
       FOR EACH ROW 
       BEGIN
       INSERT INTO mytable (thread_id) VALUES (NEW.thread_id);
       END#
DELIMITER ;`

This way, the thread_id field is set to 0 and the id field (PK AUTO INCREMENT) is set to the thread_id value (who's the id PK AI from the listening table).

indeed, the AI field is not defined in the INSERT. I solved the problem the way below:

    `DELIMITER #
CREATE DEFINER='user'@'server' TRIGGER mytrigger AFTER INSERT ON mytable
       FOR EACH ROW 
       BEGIN
       INSERT INTO mytable (id, thread_id) VALUES (null, NEW.thread_id);
       END#
DELIMITER ;`

Stating the PK id field and null value, the AI problem disappears.
I also replaced BEFORE by AFTER but i'm not sure this modification is necessary to solve the problem. I didn't test if keeping BEFORE with the other modification solve the problem at all.

Answer 3

DELIMITER $$

CREATE TRIGGER mytrigger BEFORE INSERT ON yourtable 
FOR EACH ROW BEGIN

SET NEW.thread_id = IF(ISNULL(NEW.thread_id), 0, NEW.thread_id);

END;

$$

Edit: in order to modify values of the current record, you don't use UPDATE statement, you access them by using NEW.columname