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

Spiccato

How do you uniquely identify the rows in ACCOUNTS? If edit_on is your primary key, then how can you insert duplicates? – Dogvane

I have edited the question to include the table structure. Please see. – Spiccato

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 – Spiccato

There is no status column in your schema. – Dogvane

oops.. sorry my bad. please see the edit now – Spiccato

Answer 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.

Dogvane

Does that mean I have to call the stored procedure from the trigger? – Spiccato

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 – Dogvane

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? – Spiccato

Putting two statements (insert + update) in a stored procedure. If statements execute successfully, then you commit, otherwise rollback changes. – Dogvane

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 :( – Spiccato

Maybe this would help: dev.mysql.com/doc/refman/5.0/en/… – Dogvane

Actually i figured it out. I just had to check the return value of NamedParameterJdbcTemplate.update(). Everything's fine now :) Thanks a lot for all the help – Spiccato

Could you post your solution to this page? How does it look like at finish? – Jackofalltrades

@Spiccato no, it will not work, I tried it, the best way as I think is calling the procedure after the update query – Miceli

Answer 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

Diane

Very nice solution!!! The solution suggested by pjama is fine, but it didn't fit my case, while this one works great and doesn't need a stored procedure – Postaxial

Yes, the important thing is not to write an UPDATE ACCOUNTS part, which is what MYSQL prevents. – Dessiatine

This got my downvote because it completely ignores the clearly stated request that the update should be acting on a DIFFERENT record, not on the newly inserted one. – Inheritor

Tried this and it did not work for me CREATE TRIGGER add_comment_hash_after_insert BEFORE INSERT ON comments FOR EACH ROW BEGIN SET new.comment_hash = 'aa'; END; – Testudinal

Hey Nice Solution! it's simple and works just fine for me, thanks for sharing. – Lyso

I know that the answer is not relevant for what is being asked, but people like me are getting the required solution from this answer in most cases even after 6 years. – Interstellar

Answer 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 ;

Alainaalaine

I have to say, this is a very crude way to solve this issue because it banks on the premise that no entry was added/deleted (thus increasing the autoincrement value from what the MAX(id) + 1 would yield). Regardless it's the only solution I've found to accomplish what I need to and I feel like I can bank on that premise. I'm attempting (due to moving from PHP to Django) to rename all my primary keys from TABLENAME_id to id without breaking my application. Good stuff. – Critic

This got my downvote because it completely ignores the clearly stated request that the update should be acting on a DIFFERENT record, not on the newly inserted one. – Inheritor

Answer 4

DELIMITER $$

DROP TRIGGER IF EXISTS `setEditStatus`$$
CREATE TRIGGER `setEditStatus` **BEFORE** INSERT on ACCOUNTS
FOR EACH ROW BEGIN

    SET NEW.STATUS = 'E';

END$$

DELIMITER ;

Phoney

Answer 5

On the last entry; this is another trick:

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

Heaume

Can you explain your answer a bit in an edit? Thanks. – Oxyacetylene

Answer 6

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

Bochum