How to Update Rows in the Same Table Using Triggers: A Solution to Overlapping Time Periods?

Updating Another Row in the Same Table Using Triggers

The Problem:

Maintaining records with overlapping time periods often requires updating the end date of previous records when a new one is inserted with a startDate that overlaps the existing range.

The Original Attempt:

The provided trigger attempts to update the endDate of an existing row with the value ADDDATE(NEW.startDate, -1) where the procKey matches the NEW row and the endDate is 20501231. However, this approach results 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."

The Solution:

Unfortunately, triggers cannot directly update other rows in the same table as the row that triggered the trigger. Instead, an alternative approach is required.

Using a Stored Procedure:

Create a stored procedure that encapsulates the following logic:

1. Insert or update the new row into the split table.
2. Update the endDate of the previous row with the procKey matching the NEW row and the endDate that matches the future date (e.g., 20501231) to be ADDDATE(NEW.startDate, -1).
3. Commit the transaction to ensure the changes are persistent.

This approach allows for updates to multiple rows in the same table within a single transaction, avoiding the conflicts that can arise with triggers.

The above is the detailed content of How to Update Rows in the Same Table Using Triggers: A Solution to Overlapping Time Periods?. For more information, please follow other related articles on the PHP Chinese website!