如何检查所有相关记录是否满足MYSQL触发器中的指定要求？

zd287kbt 于 2023-03-28 发布在 Mysql

关注(0)|答案(2)|浏览(117)

我有两张table;bookings和tasks。每个预订都有一对多任务。
bookings表：

+--------------+----------------+------+-----+----------------+
| 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表：

+--------------+----------------+------+-----+----------------+
| 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   |     |                |
+--------------+----------------+------+-----+----------------+

我想创建一个触发器，以便当每个任务的complete字段设置为TRUE时，相关预订的complete字段也设置为TRUE。我不知道如何检查特定bookingID的所有tasks. complete字段。

mysql

来源：https://stackoverflow.com/questions/75855654/how-to-check-if-all-related-records-meet-a-specified-requirement-in-a-mysql-trig

2条答案

按热度按时间

qgelzfjb1#

我不知道如何检查所有任务。完成特定bookingID的字段。
对于这一部分，检查complete = False的行数是否为0：

SELECT 0 = COUNT(*) FROM tasks WHERE complete = False AND bookingID = <your_booking_id>

赞(0）回复(0）举报 2023-03-28

yacmzcpb2#

下面是一个带有AFTER UPDATE TRIGGER的示例。
您还需要一个具有相同代码的AFTER INSERT TRIGGER

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

预订ID	客户端ID	车辆ID	日期	完备
1	1	1	2023-03-27 2023-03-27	0

一个9个1x一个10个1x一个11个1x
| 预订ID|客户端ID|车辆ID|日期|完备|
| --------------|--------------|--------------|--------------|--------------|
| 1|1|1|2023-03-27 2023-03-27|1|
fiddle

赞(0）回复(0）举报 2023-03-28

我来回答

如何检查所有相关记录是否满足MYSQL触发器中的指定要求？

2条答案

相关问题

热门标签

最新问答