Home >Database >Mysql Tutorial >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!