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

How do you uniquely identify the rows in ACCOUNTS? If edit_on is your primary key, then how can you insert duplicates? — pjama
– pjama, Commented Oct 13, 2012 at 23:21
I have edited the question to include the table structure. Please see. — th3an0maly
– th3an0maly, Commented Oct 13, 2012 at 23:26
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 — th3an0maly
– th3an0maly, Commented Oct 13, 2012 at 23:36

Community · Accepted Answer · 2017-05-23 11:46:16Z

34

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.

edited May 23, 2017 at 11:46

CommunityBot

11 silver badge

answered Oct 14, 2012 at 0:04

pjama

3,0443 gold badges29 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

th3an0maly Over a year ago

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

pjama Over a year ago

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

th3an0maly Over a year ago

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?

pjama Over a year ago

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

th3an0maly Over a year ago

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

|

Yves M. · Accepted Answer · 2020-03-23 13:32:12Z

16

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

edited Mar 23, 2020 at 13:32

Yves M.

31.3k24 gold badges111 silver badges154 bronze badges

answered Apr 10, 2015 at 10:32

Rangel

3263 silver badges3 bronze badges

6 Comments

Igor S Om Over a year ago

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

Spaceploit Over a year ago

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

Ellert van Koperen Over a year ago

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.

kp123 Over a year ago

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;

Alvaro Castro Over a year ago

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

|

JCLG · Accepted Answer · 2013-06-18 03:15:17Z

5

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 ;

answered Jun 18, 2013 at 3:15

JCLG

832 silver badges2 bronze badges

2 Comments

Benjamin Oman Over a year ago

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.

Ellert van Koperen Over a year ago

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.

Nuwan Alawatta · Accepted Answer · 2019-10-21 10:55:06Z

2

DELIMITER $$

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

    SET NEW.STATUS = 'E';

END$$

DELIMITER ;

edited Oct 21, 2019 at 10:55

Nuwan Alawatta

1,86521 silver badges29 bronze badges

answered Aug 1, 2019 at 11:10

KenZo

391 bronze badge

Comments

Will · Accepted Answer · 2016-03-28 10:36:20Z

1

On the last entry; this is another trick:

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

edited Mar 28, 2016 at 10:36

Will

24.8k14 gold badges100 silver badges111 bronze badges

answered Mar 28, 2016 at 10:28

Raymond B

371 bronze badge

1 Comment

Will Over a year ago

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

Chitraranjan Ngangbamcha · Accepted Answer · 2018-07-13 10:58:08Z

0

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

answered Jul 13, 2018 at 10:58

Chitraranjan Ngangbamcha

693 bronze badges

Collectives™ on Stack Overflow

MySQL - Trigger for updating same table after insert

6 Answers 6

9 Comments

6 Comments

2 Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

9 Comments

6 Comments

2 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related