MySQL trigger set values for NEW row and update another in the same table

Solution 1:

The answer to this might be unwelcome, but it is: You can't do that.

A Trigger can't update another row of the same table as the row, the trigger was called from.

The typical way to do that, is to create a stored procedure, that inserts into/Updates the target table, then updates the other row(s), all in a transaction.

Solution 2:

If you have a UNIQUE KEY defined on (procKey,EndDate), then perhaps you can remove the second line of the trigger. Also remove the hardcoded date from the trigger.

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;
END$$

and do an INSERT ON DUPLICATE KEY UPDATE like this:

INSERT INTO im.split ...
ON DUPLICATE KEY UPDATE
endDate = ADDDATE(startDate, -1);

You may also want to define endDate in im.split as follows

enddate DATE DEFAULT '2050-12-31'

Solution 3:

I managed to get it to work, by creating a "wrapper table" which is defined with the same fields as your target table, but by using the FEDERATED storage engine. The federated server I defined to target the same mysql server/itself, so at "localhost". The trigger I then made change a row in the wrapper table. Of course you have to be very sure what you do in the trigger, to avoid a recursive loop. Also potentially the performance isn't that good; I haven't tested performance, but it has been working for years in a production environment.

Solution 4:

From the MySQL Docs:

Within a stored function or trigger, it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.

You will need to find some other way of doing what you're trying to do.