MySQL - Trigger for updating same table after insert

how to write after insert event update trigger on the same table in mysql
mysql trigger after insert or update
trigger to update column in same table
mysql trigger after insert, update another table
mysql trigger before insert update same row
how to update table using trigger
sqlite trigger update same table
mysql trigger after update same row

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

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.

Create Trigger that updates the same table before (or after ??) the , CREATE TRIGGER `populate_country_id` BEFORE INSERT ON `city` FOR EACH ROW SET NEW.country_id = (SELECT CASE WHEN NEW.country_id = 0​  DELIMITER $$ CREATE TRIGGER occupy_trig AFTER INSERT ON `OccupiedRoom` FOR EACH ROW begin DECLARE id_exists Boolean; -- Check BookingRequest table SELECT 1 INTO @id_exists FROM BookingRequest WHERE BookingRequest.idRequest= NEW.idRequest; IF @id_exists = 1 THEN UPDATE BookingRequest SET status = '1' WHERE idRequest = NEW.idRequest; END IF; END; $$

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

TRIGGER that affects the same table? - Databases, AFTER UPDATE, I want my trigger to SET the URL of any row to the URL of the CREATE TRIGGER aft_INS_Table2 AFTER INSERT ON `Table 2` FOR in Mysql, by calling a stored procedure that does the actual update. Yes, you can't select from the same table in the trigger else it will mutate, but (in Oracle at least anyway), one would just use a before insert trigger and set :NEW.one_word. If you are updating then you will obviously change the trigger to suit.

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 ;

MySQL Database Triggers: Inserting or updating the same table, MySQL Database Triggers: Inserting or updating the same table a trigger, or another way so long as it is automatically updated after an insert. MySQL AFTER UPDATE triggers are invoked automatically after an update event occurs on the table associated with the triggers. The following shows the syntax of creating a MySQL AFTER UPDATE trigger: CREATE TRIGGER trigger_name AFTER UPDATE ON table_name FOR EACH ROW trigger_body

On the last entry; this is another trick:

SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_schema = ... and table_name = ...

trigger updating another row of same table, I need that to eliminate double manual insert/update. lets say this is very simple version of this trigger: CREATE TRIGGER auto_list AFTER  MySQL AFTER INSERT triggers are automatically invoked after an insert event occurs on the table. The following shows the basic syntax of creating a MySQL AFTER INSERT trigger: CREATE TRIGGER trigger_name AFTER INSERT ON table_name FOR EACH ROW trigger_body

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

MySQL Multiple Triggers for the Same Event & Action Time, Before MySQL version 5.7.2, you can only create one trigger for an event in a table can only create one trigger for the BEFORE UPDATE or AFTER UPDATE event. To achieve this, you can add an additional column to the PriceLogs table. 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

MySQL AFTER INSERT Trigger By Practical Examples, MySQL AFTER INSERT triggers are automatically invoked after an insert event message) VALUES(new.id,CONCAT('Hi ', NEW.name, ', please update your date NULL , therefore, the trigger inserted only one row into the reminders table. A trigger is a set of actions that are run automatically when a specified change operation (SQL INSERT , UPDATE , DELETE or TRUNCATE statement) is performed on a specified table. Triggers are useful for tasks such as enforcing business rules, validating input data, and keeping an audit trail.

How to create a trigger to update on same table in MySQL or Oracle , You can use a trigger on a second table to update the first table (INSERT Table1 >> INSERT Table2 >>UPDATE Table1) ; a really bad practice. 2.1K views · 

MySQL - Trigger for updating same table after insert, 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 

Comments
  • How do you uniquely identify the rows in ACCOUNTS? If edit_on is your primary key, then how can you insert duplicates?
  • I have edited the question to include the table structure. Please see.
  • if edit_on = 123 for a row where pk = 456, that means 456 is an edit on 123. Therefore, status should be updated to 'E' for 123
  • There is no status column in your schema.
  • oops.. sorry my bad. please see the edit now
  • Does that mean I have to call the stored procedure from the trigger?
  • No, I believe you move the original INSERT query into the stored procedure, and call the proc instead of the query. Here's the syntax for MySQL: dev.mysql.com/doc/refman/5.0/en/call.html
  • I'm a little confused here. Do you mean move the original insert into a procedure and use a trigger to make the updates, or moving the whole logic into a stored procedure?
  • Putting two statements (insert + update) in a stored procedure. If statements execute successfully, then you commit, otherwise rollback changes.
  • good news. I've implemented that. It works fine. Except for the fact that I cant get the status (if or not the SP changed any rows) in my JDBC Template :(