mysql - 触发器只允许租车最多 7 天 Max

Question

我想创建一个触发器，将租车时间限制为 7 天。因此，例如......您可以在 01/01/2019 - 06/01/2019 期间租车，但是您不能在 01/01/2019 至 10/01/2019 期间租车。我使用 BEFORE INSERT 吗？感谢您的帮助。

为了清楚起见，我还附上了我的预订表。

insert into bookings (booking_id, booking_date, start_date, end_date, invoice_no, chauffeur_id, vehicle_id, customer_id, chauffeur_req,special_instructions) values (1, '2019/02/26', '2019/02/28', '2019/03/01', 1, 1, 1, 1, 'Yes', 'Be a bit early');
insert into bookings (booking_id, booking_date, start_date, end_date, invoice_no, chauffeur_id, vehicle_id, customer_id, chauffeur_req,special_instructions) values (2, '2019/02/28', '2019/02/28', '2019/03/03', 2, NULL, 2, 2, 'No','Please have some water');
insert into bookings (booking_id, booking_date, start_date, end_date, invoice_no, chauffeur_id, vehicle_id, customer_id, chauffeur_req,special_instructions) values (3, '2019/03/01', '2019/03/02', '2019/03/05', 3, NULL, 3, 3, 'No','Drive slow, children in the car');
insert into bookings (booking_id, booking_date, start_date, end_date, invoice_no, chauffeur_id, vehicle_id, customer_id, chauffeur_req,special_instructions) values (4, '2019/03/04', '2019/03/06', '2019/03/10', 4, 4, 4, 4, 'Yes','Please be silent while driving');
insert into bookings (booking_id, booking_date, start_date, end_date, invoice_no, chauffeur_id, vehicle_id, customer_id, chauffeur_req,special_instructions) values (5, '2019/03/08', '2019/03/09', '2019/03/13', 5, NULL, 5, 5, 'No','Need to be exactly on time');
insert into bookings (booking_id, booking_date, start_date, end_date, invoice_no, chauffeur_id, vehicle_id, customer_id, chauffeur_req,special_instructions) values (6, '2019/04/03', '2019/04/04', '2019/04/06', 6, 6, 6, 6, 'Yes','Children onboard, please drive slow');
insert into bookings (booking_id, booking_date, start_date, end_date, invoice_no, chauffeur_id, vehicle_id, customer_id, chauffeur_req,special_instructions) values (7, '2019/04/05', '2019/04/07', '2019/03/10', 7, NULL, 7, 7, 'No','Arrive 5 minutes early');

Answer 1

我们可以在 BEFORE INSERT 触发器中执行此检查，并引发错误条件以防止语句在前面。为了完整起见，我们可能还需要一个 BEFORE UPDATE 触发器(以防止已经插入 bookings 的行被更改为超过一周。)

像这样开始:

DELIMITER $$

CREATE TRIGGER `trg_bookings_bi` 
BEFORE INSERT ON `bookings`
FOR EACH ROW
BEGIN
   IF ( NEW.end_date > NEW.start_date + INTERVAL 7 DAY ) THEN
      SIGNAL SQLSTATE '45000'
         SET MESSAGE_TEXT = 'error end_date more than seven days after start_date';
   END IF;   
END$$

DELIMITER ;