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!