How to Update an Existing Row in MySQL After an Insert Without Trigger Locking?

MySQL Trigger for Updating Existing Row in Same Table After Insert

Problem Statement

When a new row is inserted into the ACCOUNTS table, a corresponding existing row in the same table with pk equal to the edit_on column of the newly inserted row needs to be updated by setting its status column to 'E'. However, an error occurs when attempting this update within the trigger, stating that the table is already being used by the statement that invoked the trigger.

Solution

Due to the restriction on updating a table within a trigger that is already being modified by the triggering statement, an alternative approach is required.

Stored Procedure

Create a stored procedure that handles both the insertion and updating operations in a single transaction. The steps involved are as follows:

1. Insert the new row into the ACCOUNTS table.
2. Update the existing row where pk matches the edit_on column of the newly inserted row, setting the status column to 'E'.
3. Commit the transaction to finalize the changes.

SQL Syntax

CREATE PROCEDURE setEditStatus(IN NEW_pk INT, IN NEW_edit_on INT)
BEGIN
  /* Insert new row into ACCOUNTS table */
  INSERT INTO ACCOUNTS (pk, user_id, edit_on, status)
  VALUES (NEW_pk, /* User ID */, NEW_edit_on, 'A');

  /* Update existing row */
  UPDATE ACCOUNTS
  SET status = 'E'
  WHERE pk = NEW_edit_on;

  /* Commit changes */
  COMMIT;
END;

Trigger

Modify the trigger to call the stored procedure instead of attempting the update directly.

DELIMITER $$

DROP TRIGGER IF EXISTS `setEditStatus`$$
CREATE TRIGGER `setEditStatus` AFTER INSERT on ACCOUNTS
FOR EACH ROW BEGIN
  CALL setEditStatus(NEW.pk, NEW.edit_on);
END$$

DELIMITER ;

This workaround allows for the desired update to be performed without violating the restriction on updating the same table within a trigger.

The above is the detailed content of How to Update an Existing Row in MySQL After an Insert Without Trigger Locking?. For more information, please follow other related articles on the PHP Chinese website!