How to Create a Trigger to Update a Column in Another Table Based on an INSERT Operation?

Trigger to Update Another Table's Column

In your database, you have three tables: BookingRequest, status, and OccupiedRoom. You desire to create a trigger that changes the status column in the BookingRequest table to '1' whenever a request with the same ID is added to the OccupiedRoom table.

You previously attempted the following trigger:

create trigger occupy_trig after insert on OccupiedRoom 
for each row
begin
  if BookingRequest.idRequest= NEW.idRequest
   then
       update BookingRequest
       set status = '1';
       where idRequest = NEW.idRequest;

    end if;
END;

However, it was unsuccessful. Here's a modified version that should work:

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 ;

Explanation:

• The trigger is defined after an insert operation on the OccupiedRoom table.
• A local variable @id_exists is introduced to check if the requesting ID exists in the BookingRequest table.
• A subquery is used to determine whether an entry with the matching ID already exists in the BookingRequest table.
• If the ID exists (@id_exists = 1), the BookingRequest table is updated by setting status = '1' where idRequest matches the ID of the new entry in OccupiedRoom.
• Remember to adjust table names and potential schema or field name differences accordingly.

The above is the detailed content of How to Create a Trigger to Update a Column in Another Table Based on an INSERT Operation?. For more information, please follow other related articles on the PHP Chinese website!