How to Update a BookingRequest Status After Inserting into OccupiedRoom Using a MySQL Trigger?

MySQL After Insert Trigger Updates Another Table's Column

When creating a booking request, it's necessary to update the status in the BookingRequest table based on the insertion of a corresponding request into the OccupiedRoom table. To achieve this, a trigger is required.

To begin with, define the following tables:

<br>  -- BookingRequest table:<br>  CREATE TABLE BookingRequest (</p>
<pre class="brush:php;toolbar:false">idRequest INT NOT NULL AUTO_INCREMENT,
roomClass INT NOT NULL,
inDate DATE NOT NULL,
outDate DATE NOT NULL,
numOfBeds INT NOT NULL,
status INT NOT NULL,
idUser INT NOT NULL,
PRIMARY KEY (idRequest),
INDEX idx_status (status),
INDEX idx_user (idUser)

);

-- Status table:
CREATE TABLE Status (

idStatus INT NOT NULL AUTO_INCREMENT,
nameStatus ENUM('underConsideration', 'approved', 'rejected'),
PRIMARY KEY (idStatus)

);

-- OccupiedRoom table:
CREATE TABLE OccupiedRoom (

idOccupation INT NOT NULL AUTO_INCREMENT,
idRoom INT NOT NULL,
idRequest INT NOT NULL,
PRIMARY KEY (idOccupation),
INDEX idx_id_room (idRoom),
INDEX idx_id_request (idRequest)

);

Now, let's write 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 ;

Here's how it works:

1. Upon inserting a record into the OccupiedRoom table, the trigger is executed.
2. It checks if a matching record exists in the BookingRequest table via a subquery.
3. If a match is found, the status column in the BookingRequest table is updated to '1'.

The above is the detailed content of How to Update a BookingRequest Status After Inserting into OccupiedRoom Using a MySQL Trigger?. For more information, please follow other related articles on the PHP Chinese website!