MySQL - Trigger to update the same table after insert operation
P粉419164700
P粉419164700 2023-10-16 20:01:46
0
2
749

This is what I'm trying to do:

When there is a new INSERT into table ACCOUNTS, I need to update ACCOUNTS by setting status='E'phpcnend line where pk = NEW.edit_on cphpcn 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 ;

Requirement is not my operation newly inserted column, but already existing column, where pk = 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

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


P粉419164700
P粉419164700

reply all(2)
P粉063862561

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

activationCode and email are rows in table USER. When inserting, I did not specify a value for activationCode, which will be dynamically created by MySQL.

Change username to your MySQL username and db_name to 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
P粉262113569

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

Based on this answer, it seems you should:

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

Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template