MySQL - Trigger for updating same table after insert
This is how I update a row in the same table on insert
activationCode
and email
are rows in the table USER
.
On insert I don't specify a value for activationCode
, it will be created on the fly by MySQL.
Change username
with your MySQL username and db_name
with your db name.
CREATE DEFINER=`username`@`localhost`
TRIGGER `db_name`.`user_BEFORE_INSERT`
BEFORE INSERT ON `user`
FOR EACH ROW
BEGIN
SET new.activationCode = MD5(new.email);
END
It seems that you can't do all this in a trigger. According to the documentation:
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.
According to this answer, it seems that you should:
create a stored procedure, that inserts into/Updates the target table, then updates the other row(s), all in a transaction.
With a stored proc you'll manually commit the changes (insert and update). I haven't done this in MySQL, but this post looks like a good example.