How to Update Previous and New Rows in a MySQL Table Using Stored Procedures?

Modifying Previous and New Rows in a MySQL Table Using Triggers

Problem:

In a table tracking fees for items, you want to insert new fee records while simultaneously updating the endDate of previous records with the same procKey to end one day before the startDate of the new record.

Initial Approach:

You attempted to create a trigger with the following code:

CREATE
DEFINER=`root`@`%`
TRIGGER `im`.`splitBeforeIns`
BEFORE INSERT ON `im`.`split`
FOR EACH ROW
BEGIN
    SET NEW.tcPercent = (NEW.tcOfficeFee / NEW.globalFee) * 100 , NEW.proPercent = 100 - NEW.tcPercent, NEW.endDate = 20501231;
    UPDATE im.split set endDate = ADDDATE(NEW.startDate, -1) where procKey = NEW.procKey AND endDate = 20501231;
END$$

However, this trigger resulted in the error: "Can't update table 'split' in stored function/trigger because it is already used by statement which invoked this stored function/trigger."

Solution:

Triggers cannot modify other rows in the same table.

To achieve the desired functionality, you must use a stored procedure instead of a trigger. A stored procedure can perform multiple operations as part of a single transaction, including inserting into or updating the table you wish to modify.

Example Stored Procedure:

CREATE PROCEDURE updateSplit(IN @procKey INT, IN @startDate DATE, IN @endDate DATE)
BEGIN
    DECLARE @errorMessage VARCHAR(255);
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    START TRANSACTION;
    INSERT INTO split (procKey, startDate, endDate, tcOfficeFee, globalFee) 
    VALUES (@procKey, @startDate, @endDate, @tcOfficeFee, @globalFee);
    
    IF @@ROWCOUNT > 0 THEN
        -- Only update previous record if a new row was successfully inserted
        UPDATE split SET endDate = ADDDATE(@startDate, -1)
        WHERE procKey = @procKey AND endDate = 20501231;
        
        IF @@ROWCOUNT > 0 THEN
            COMMIT;
        ELSE
            SET @errorMessage = 'Failed to update previous record';
            ROLLBACK;
        END IF;
    ELSE
        SET @errorMessage = 'Failed to insert new record';
        ROLLBACK;
    END IF;
END

This stored procedure can be used to update the split table as desired:

CALL updateSplit(123, '2022-06-01', '2022-06-30');

The above is the detailed content of How to Update Previous and New Rows in a MySQL Table Using Stored Procedures?. For more information, please follow other related articles on the PHP Chinese website!