MySQL - Trigger for updating same table after insert
Solution 1
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.
Solution 2
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
Solution 3
Had the same problem but had to update a column with the id that was about to enter, so you can make an update should be done BEFORE and AFTER not BEFORE had no id so I did this trick
DELIMITER $$
DROP TRIGGER IF EXISTS `codigo_video`$$
CREATE TRIGGER `codigo_video` BEFORE INSERT ON `videos`
FOR EACH ROW BEGIN
DECLARE ultimo_id, proximo_id INT(11);
SELECT id INTO ultimo_id FROM videos ORDER BY id DESC LIMIT 1;
SET proximo_id = ultimo_id+1;
SET NEW.cassette = CONCAT(NEW.cassette, LPAD(proximo_id, 5, '0'));
END$$
DELIMITER ;
Solution 4
On the last entry; this is another trick:
SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_schema = ... and table_name = ...
Comments
-
th3an0maly almost 2 years
Here's what I'm trying to do:
When there's a new
INSERT
into the tableACCOUNTS
, I need to update the row inACCOUNTS
wherepk
=NEW.edit_on
by settingstatus='E'
to denote that the particular (old) account has been edited.DELIMITER $$ DROP TRIGGER IF EXISTS `setEditStatus`$$ CREATE TRIGGER `setEditStatus` AFTER INSERT on ACCOUNTS FOR EACH ROW BEGIN update ACCOUNTS set status='E' where ACCOUNTS.pk = NEW.edit_on ; END$$ DELIMITER ;
The requirement is NOT that I manipulate the newly inserted column, but an already existing column with
pk = NEW.edit_on
However, I can't update the same table:
Can't update table ACCOUNTS ... already used by the statement that invoked this trigger
Please suggest a workaround
PS: I have already gone through Updating table in trigger after update on the same table, Insert into same table trigger mysql, Update with after insert trigger on same table and mysql trigger with insert and update after insert on table but they dont seem to answer my question.
Edit
ACCOUNTS
Table:CREATE TABLE `ACCOUNTS` ( `pk` bigint(10) unsigned NOT NULL AUTO_INCREMENT, `user_id` bigint(9) unsigned NOT NULL, `edit_on` bigint(10) unsigned DEFAULT NULL, `status` varchar(1) NOT NULL DEFAULT 'A', PRIMARY KEY (`pk`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=2147483726 DEFAULT CHARSET=latin1