Home >Database >Mysql Tutorial >How to Update a Row in the Same Table after Inserting a New Row in MySQL?

How to Update a Row in the Same Table after Inserting a New Row in MySQL?

Linda Hamilton
Linda HamiltonOriginal
2024-10-30 06:58:18862browse

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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn