How Can I Update the Same Table After an Insert Without Trigger Errors?

Trigger for Updating Same Table after Insert: An Alternative Approach

Issue Overview

You seek to implement a trigger on the ACCOUNTS table that updates a column in the same table based on the edit_on value of a newly inserted row. However, you encounter an error due to the restriction against updating the same table within a trigger that invoked it.

Alternative Workaround: Stored Procedure

To circumvent this limitation, a stored procedure can be employed to perform both the insert and update operations within a single transaction. This approach allows for manual commit of changes, enabling the desired updates.

Stored Procedure Implementation

Here's an example stored procedure that achieves your goal:

DELIMITER $$
CREATE PROCEDURE InsertAndEditStatus(IN new_pk BIGINT)
BEGIN
  -- Insert new row into ACCOUNTS
  INSERT INTO ACCOUNTS (pk, user_id, edit_on, status)
  VALUES (new_pk, /* user_id */, NULL, 'A');
  
  -- Update existing row in ACCOUNTS
  UPDATE ACCOUNTS SET status='E' WHERE pk = NEW.edit_on;
  
  -- Commit transaction
  COMMIT;
END$$
DELIMITER ;

Usage

To use this stored procedure, call it with the primary key of the newly inserted row as an argument:

CALL InsertAndEditStatus(2147483726);

This procedure will insert a new row with the specified primary key and default values, then update the existing row referenced by the edit_on column in the newly inserted row to indicate that it has been edited.

The above is the detailed content of How Can I Update the Same Table After an Insert Without Trigger Errors?. For more information, please follow other related articles on the PHP Chinese website!