Home >Database >Mysql Tutorial >How to Update a Row in the Same Table after Inserting a New Row in MySQL?
MySQL Trigger: Setting Values for NEW Row and Updating Another in the Same Table
In MySQL, triggers are powerful mechanisms that allow you to perform specific actions when certain events occur within a table. One common use case is to set values for the newly inserted row (NEW) while also updating another row in the same table. However, a common error can occur when attempting this operation:
ERROR 1442: Can't update table 'split' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
This error arises because a trigger cannot update the same table from which it was triggered. To overcome this limitation, follow this alternative approach:
Using Stored Procedure:
Instead of using a trigger, create a stored procedure that handles the insertion and update operations. Here's an example:
<code class="sql">DELIMITER $$ CREATE PROCEDURE split_before_ins(IN startDate DATE, IN endDate DATE, IN tcOfficeFee DECIMAL, IN globalFee DECIMAL) BEGIN -- Insert a new row with the provided parameters INSERT INTO im.split (startDate, endDate, tcOfficeFee, globalFee) VALUES (startDate, endDate, tcOfficeFee, globalFee); -- Update the previous row's endDate UPDATE im.split SET endDate = DATE_SUB(startDate, INTERVAL 1 DAY) WHERE procKey = (SELECT procKey FROM im.split ORDER BY procKey DESC LIMIT 1) AND endDate = '20501231'; END$$ DELIMITER ;</code>
Procedure Usage:
To invoke the stored procedure, execute the following:
<code class="sql">CALL split_before_ins('2023-01-01', '2024-12-31', 10.00, 100.00);</code>
This stored procedure approach allows you to perform the desired operations in a single transactional context, avoiding the error encountered when using a trigger to update the same table from which it was called.
The above is the detailed content of How to Update a Row in the Same Table after Inserting a New Row in MySQL?. For more information, please follow other related articles on the PHP Chinese website!