Question 20296 in Mysql Question posted in
The official MySQL documentation can be found here.

How to check if all related records meet a specified requirement in a MYSQL trigger?

I have two tables; bookings and tasks. Each booking has 1-to-many tasks.

bookings table:

+--------------+----------------+------+-----+----------------+
| Field        | Type           | Null | Key | Extra          |
+--------------+----------------+------+-----+----------------+
| bookingsID   | int(8)         | NO   | PRI | auto_increment |
| clientID     | int(8)         | NO   | FK  |                |
| vehicleID    | int(8)         | NO   | FK  |                |
| date         | date           | NO   |     |                |
| complete     | tinyint(1)     | NO   |     |                |
+--------------+----------------+------+-----+----------------+

tasks table:

+--------------+----------------+------+-----+----------------+
| Field        | Type           | Null | Key | Extra          |
+--------------+----------------+------+-----+----------------+
| taskID       | int(8)         | NO   | PRI | auto_increment |
| bookingID    | int(8)         | NO   | FK  |                |
| description  | text           | NO   |     |                |
| price        | decimal(10, 2) | NO   |     |                |
| complete     | tinyint(1)     | NO   |     |                |
+--------------+----------------+------+-----+----------------+

I would like to create a trigger so that when the complete field for each task is set to TRUE, the complete field of the related booking is also set to TRUE. I’m not sure how to check all tasks.complete fields for a specific bookingID.

Tags:

2

Answers


  1. 0

    I’m not sure how to check all tasks.complete fields for a specific bookingID.

    For this part, how about checking that the count of rows where complete = False is 0:

    SELECT 0 = COUNT(*) FROM tasks WHERE complete = False AND bookingID = <your_booking_id>
    Login or Signup to reply.
  2. 0

    Here is a sample with an AFTER UPDATE TRIGGER.

    You will also need a AFTER INSERT TRIGGER with the same code

    CREATE TABLE bookings (
   bookingsID   int(8)   PRIMARY KEY  auto_increment ,
 clientID      int(8) ,
 vehicleID     int(8),
 `date`          date,
 complete      tinyint(1)  )

;

    INSERT INTO bookings VALUES(1,1,1,NOW(), 0)

    CREATE TABLE tasks
(taskID  int(8)   PRIMARY KEY  auto_increment,
 bookingID     int(8),
 description   text,
 price         decimal(10, 2) ,
 complete      tinyint(1) )

    INSERT INTO tasks VALUES(NULL, 1,'test',1.0,0),(NULL, 1,'test2',1.0,0)

    Records: 2  Duplicates: 0  Warnings: 0

    CREATE TRIGGER trigger_name AFTER UPDATE
ON tasks FOR EACH ROW  
BEGIN  
   IF NOT EXISTS ( SELECT 1 FROM tasks WHERE bookingID = NEW.bookingID AND complete = 0) THEN
        UPDATE bookings SET complete = 1 WHERE bookingsID = NEW.bookingID;
  END IF;
END

    UPDATE tasks SET complete = 1 WHERE taskID = 1

    Rows matched: 1  Changed: 1  Warnings: 0

    SELECT * FROM bookings
    bookingsID clientID vehicleID date complete
    1 1 1 2023-03-27 0 
    UPDATE tasks SET complete = 1 WHERE taskID = 2

    Rows matched: 1  Changed: 1  Warnings: 0

    SELECT * FROM bookings
    bookingsID clientID vehicleID date complete
    1 1 1 2023-03-27 1

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search