Can MySQL Triggers Update the Same Table They Are Called From?

MySQL Triggers: Limitations with Updating Same Table Rows

Question: How do you set values for a new row in a MySQL trigger and update another row in the same table?

An attempt was made to create a trigger that performed the following operations upon row insertion:

1. Calculate and set specific values for the new row.
2. Set the endDate of the previous row to one day before the startDate of the new row.

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

Answer:

Unfortunately, the desired action cannot be performed using a trigger. MySQL triggers are constrained in their ability to update the same table they were called from. This limitation is imposed to prevent data integrity issues that could arise from simultaneous updates.

The recommended approach to achieve the desired functionality is to create a stored procedure that encapsulates both the insertion and update operations within a single transaction. This ensures that the database remains in a consistent state throughout the process.

The above is the detailed content of Can MySQL Triggers Update the Same Table They Are Called From?. For more information, please follow other related articles on the PHP Chinese website!