How to Prevent Duplicate Updates in a MySQL Trigger When Inserting Multiple Rows?

MySQL Trigger to Update Column in Another Table upon Insert

In a relational database system, you may encounter the need to automatically perform an operation on one table when a change occurs in another related table. This is where triggers come in handy. In this specific instance, we aim to create a trigger in MySQL that updates a column in the BookingRequest table when a new row is inserted into the OccupiedRoom table.

The trigger you presented attempts to update the status column in the BookingRequest table directly after inserting a row into the OccupiedRoom table. However, the issue with this approach is that the trigger is defined to execute for each inserted row in the OccupiedRoom table, which means that it will be executed multiple times if multiple rows are inserted simultaneously. This can lead to incorrect or inconsistent data updates.

To resolve this issue, we need to modify the trigger to only update the status column in the BookingRequest table if a corresponding row already exists. Here's a revised version of the trigger:

DELIMITER $$
CREATE TRIGGER occupy_trig
AFTER INSERT ON `OccupiedRoom` FOR EACH ROW
begin
  DECLARE id_exists Boolean;
  -- Check BookingRequest table
  SELECT 1
  INTO @id_exists
  FROM BookingRequest
  WHERE BookingRequest.idRequest= NEW.idRequest;

  IF @id_exists = 1
  THEN
    UPDATE BookingRequest
    SET status = '1'
    WHERE idRequest = NEW.idRequest;
  END IF;
END;
$$
DELIMITER ;

This trigger utilizes a subquery to check if a corresponding row exists in the BookingRequest table based on the idRequest column. If a matching row is found, the trigger proceeds to update the status column to '1'. By incorporating this check, we ensure that the trigger only updates relevant rows in the BookingRequest table and eliminates the potential for incorrect or duplicate updates.

The above is the detailed content of How to Prevent Duplicate Updates in a MySQL Trigger When Inserting Multiple Rows?. For more information, please follow other related articles on the PHP Chinese website!