1

I am using the BEFORE INSERT event trigger using stored procedure in MySQL to update the new row once it gets inserted. I am fetching data from different sources.

The problem is when the multiple rows get inserted simultaneously, it updates the same value in both the irrelevant rows.

Please find the following trigger code for the reference.

BEGIN
    DECLARE tmpId INT;
    SELECT AUTO_INCREMENT INTO tmpId FROM `information_schema`.`tables` WHERE `table_schema` = 'demo_db' and `table_name` = 'test_users';
IF (NEW.parent_id IS NULL) THEN
    SET NEW.parent_id = tmpId;
END IF;
END

Can any please help?

K.Raj.
  • 643
  • 6
  • 22
  • 1
    Why do you need a trigger for this? – Lord Elrond Feb 28 '20 at 05:33
  • @ReinstateMonica I want to update the same row after insert. Unfortunately, there are so many operations that are being done in the code. Also, the code is being used in so many modules. So, I have found the generic way to update it whenever a new row gets inserted. – K.Raj. Feb 28 '20 at 05:38
  • 1
    Why do you need it to auto increment? – Lord Elrond Feb 28 '20 at 05:39
  • @ReinstateMonica To get the next Auto Incremental Id before inserting the row. Otherwise, it is unable to update the new id once it gets inserted. The following are the links I had used to get the solution. https://stackoverflow.com/questions/12877732/mysql-trigger-for-updating-same-table-after-insert https://stackoverflow.com/questions/11247590/mysql-trigger-set-values-for-new-row-and-update-another-in-the-same-table – K.Raj. Feb 28 '20 at 05:44
  • Try [this answer](https://stackoverflow.com/a/24405151/10746224) – Lord Elrond Feb 28 '20 at 05:46
  • @ReinstateMonica Thank you so much. But, my trigger is working correctly. Unfortunately, it goes wrong and updates the same ID whenever 2 or more rows get inserted at the exact same time. Even all the new rows belong to different parrent_id – K.Raj. Feb 28 '20 at 05:50
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/208687/discussion-between-k-raj-and-reinstate-monica). – K.Raj. Feb 28 '20 at 05:57
  • I still don't get why you think you need a BEFORE trigger here. Why not use last_insert_id? – Strawberry Feb 28 '20 at 07:52
  • @Strawberry Because MySQL does not allow to update the value in AFTER trigger for the same row. My question is how can I prevent this trigger to update the same value for simultaneously inserted rows. Could you please help with this? – K.Raj. Feb 28 '20 at 08:20

0 Answers0