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, Oct 13 '12 at 23:21
I have edited the question to include the table structure. Please see. — th3an0maly, Oct 13 '12 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, Oct 13 '12 at 23:36

score 32 · Accepted Answer · edited May 23 '17 at 11:46

32

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 '17 at 11:46

Community

1
1

answered Oct 14 '12 at 00:04

pjama

3,014
3
26
27

Does that mean I have to call the stored procedure from the trigger? – th3an0maly Oct 14 '12 at 00:08
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: http://dev.mysql.com/doc/refman/5.0/en/call.html – pjama Oct 14 '12 at 01:59
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? – th3an0maly Oct 14 '12 at 08:39
1

Putting two statements (insert + update) in a stored procedure. If statements execute successfully, then you `commit`, otherwise `rollback` changes. – pjama Oct 14 '12 at 19:27
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 :( – th3an0maly Oct 14 '12 at 20:09
Maybe this would help: http://dev.mysql.com/doc/refman/5.0/en/stored-routines-last-insert-id.html – pjama Oct 14 '12 at 20:24
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 – th3an0maly Oct 14 '12 at 20:25
Could you post your solution to this page? How does it look like at finish? – user2602807 Oct 05 '16 at 06:56
@th3an0maly no, it will not work, I tried it, the best way as I think is calling the procedure after the update query – Achintha Isuru Dec 13 '19 at 18:34

score 13 · Answer 2 · edited Mar 23 '20 at 13:32

13

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 '20 at 13:32

Yves M.

29,855
23
108
144

answered Apr 10 '15 at 10:32

Rangel

288
3
3

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 – Igor S Om Apr 09 '18 at 10:43
Yes, the important thing is not to write an UPDATE ACCOUNTS part, which is what MYSQL prevents. – Spaceploit Jun 15 '18 at 18:45
3

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. – Ellert van Koperen Dec 31 '18 at 15:26
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; – kp123 Sep 25 '19 at 18:31
Hey Nice Solution! it's simple and works just fine for me, thanks for sharing. – Alvaro Castro Sep 08 '20 at 05:56
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. – Aakash Sahai Sep 28 '21 at 11:06

score 5 · Answer 3 · answered Jun 18 '13 at 03:15

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 '13 at 03:15

JCLG

83
2
2

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. – Benjamin Oman Jul 03 '13 at 09:23
2

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. – Ellert van Koperen Dec 31 '18 at 15:27

score 2 · Answer 4 · edited Oct 21 '19 at 10:55

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 '19 at 10:55

Nuwan Alawatta

1,812
21
29

answered Aug 01 '19 at 11:10

KenZo

39
1

score 1 · Answer 5 · edited Mar 28 '16 at 10:36

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 '16 at 10:36

Will

24,082
14
97
108

answered Mar 28 '16 at 10:28

Raymond B

29
1

Can you explain your answer a bit in an edit? Thanks. – Will Mar 28 '16 at 10:36

score 0 · Answer 6 · answered Jul 13 '18 at 10:58

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 '18 at 10:58

Chitraranjan Ngangbamcha

29
3

MySQL - Trigger for updating same table after insert

6 Answers6

Linked

Related