mysql - 如何使用 IF 语句为损坏的汽车创建触发器

Question

我想创建一个触发器，如果​​汽车当前正在维修，则不允许租赁汽车。我对触发器很陌生......有人能解释一下触发器的这方面吗？我的假设是......

DELIMITER $$
CREATE TRIGGER `inspections_const` BEFORE UPDATE ON `inspections` FOR EACH ROW BEGIN 
    SET NEW.booking_id = IF.repair_complete = 'No' 
                          THEN 'Allow booking'
                          ELSE 'Do not allow'  
                     END;                   
END
$$
DELIMITER ;

表是

CREATE TABLE `inspections` (
  `inspection_id` int(10) NOT NULL,
  `inspection_date` date NOT NULL,
  `vehicle_id` int(10) NOT NULL,
  `problem` varchar(50) NOT NULL,
  `repair_complete` enum('Yes','No') NOT NULL,
  `mechanic_id` int(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `bookings` (
  `booking_id` int(50) NOT NULL,
  `booking_date` date NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `invoice_no` int(10) NOT NULL,
  `chauffeur_id` int(10) DEFAULT NULL,
  `vehicle_id` int(10) NOT NULL,
  `customer_id` int(50) NOT NULL,
  `chauffeur_req` enum('Yes','No') NOT NULL,
  `special_instructions` varchar(255) NOT NULL,
  `TheDuration` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Answer 1

我没有测试它，所以它可能有缺失条件或类似的情况。但它应该可以帮助您走上正确的道路

查询

DELIMITER $$
CREATE TRIGGER `check_repair` BEFORE INSERT ON `bookings` FOR EACH ROW BEGIN 
    # We need to save the "count"
    DECLARE inspections_count INT DEFAULT 0;

    # Check if where is a repair going which matches the vehicle_id
    SELECT
      1 INTO inspections_count # store the "count" into the variable.
    FROM inspections
    WHERE
        inspections.vehicle_id = NEW.vehicle_id
      AND
        repair_complete = 'No'
    ORDER BY 
     inspections.start_date DESC
   LIMIT 1;

    # if there is a "count" stop the insert.
    IF inspections_count = 0 THEN 
      SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'vehicle is in repair';
    END IF;
END
$$
DELIMITER ;