MySQL - Trigger to update the same table after insert operation-PHP Chinese Network Q&A

This is what I'm trying to do:

When there is a newINSERTinto tableACCOUNTS, I need to updateACCOUNTSby settingstatus='E'phpcnend line wherepk=NEW.edit_oncphpcn to indicate that a specific (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 ;
Requirementis notmy operationnewly insertedcolumn, butalready existingcolumn, wherepk = NEW.edit_on< ; /p>
However, I cannot update the same table:Cannot update table ACCOUNTS ... has been used by the statement that called this trigger
Please suggest a solution
PS: I have done update table in trigger after update on same table, insert into same table trigger mysql, update on same table using trigger after insert and insert and update mysql trigger after insert on table but they don't seem to answer my question.
edit
ACCOUNTSTable:
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

reply all (2)

P粉0638625612023-10-17 18:44:15 2 floor

This is how I update rows in the same table on insert

activationCodeandemailare rows in tableUSER. When inserting, I did not specify a value foractivationCode, which will be dynamically created by MySQL.

Changeusernameto your MySQL username anddb_nameto your database 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

Like+0

Add Reply

P粉2621135692023-10-17 17:55:58 1 floor

It seems you can't do all this in a trigger. According to thedocumentation:

Based onthis answer, it seems you should:

With stored procedures, you will commit changes (inserts and updates) manually. I haven't done this in MySQL, butthis articlelooks like a good example.