MySQL - Trigger for updating same table after insert

Question

Here s what I m trying to do   When there s a new INSERT into the table ACCOUNTS  I need to update the row in ACCOUNTS where pk   NEW edit on by setting status  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

User · Answer

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

User · Answer

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

User · Answer

DELIMITER     DROP TRIGGER IF EXISTS  setEditStatus    CREATE TRIGGER  setEditStatus    BEFORE   INSERT on ACCOUNTS FOR EACH ROW BEGIN      SET NEW STATUS    E    END    DELIMITER

User · Answer

Instead you can use before insert and get max pkid for the particular table and then update the maximium pkid table record

User · Answer

On the last entry  this is another trick   SELECT AUTO INCREMENT FROM information schema tables WHERE table schema       and table name

User · Answer

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

[mysql] MySQL - Trigger for updating same table after insert

Examples related to mysql

Examples related to database-trigger