Блог

Кто-нибудь может увидеть, что не так с этим кодом?

Все работало нормально, пока я не добавил триггер для вычисления разницы между двумя датами. Прежде всего, я изменил таблицу на всякий случай, чтобы кому-нибудь было интересно.

 ALTER TABLE bookings 
ADD COLUMN TheDuration varchar(10);  
  

Тогда мои триггеры следующие…

РАЗДЕЛИТЕЛЬ //

 CREATE TRIGGER check_licence /*This trigger will approve customers with a valid licence */
BEFORE UPDATE ON customers                 
FOR EACH ROW 
BEGIN 
    SET NEW.Status = CASE WHEN NEW.valid_licence = 'Yes' 
                          THEN 'Approved'
                          ELSE 'Unapproved'  /*So if a Customer has a valid licence, He will be automatically approved. */
                                        /*But if he doesn't he will become unapproved[WORKING]*/

SET NEW.TheDuration = DATEDIFF(NEW.bookings.end_date, NEW.bookings.start_date)   -- -- TO CALCULATE DURATION BETWEEN 2 DATES
END;
//  
DELIMITER ;
  

Проблема заключается в

 SET NEW.TheDuration = DATEDIFF(NEW.bookings.end_date, NEW.bookings.start_date)   -- -- TO CALCULATE DURATION BETWEEN 2 DATES
  

Поскольку все работало до того, как я добавил это.

То же самое здесь…

 DELIMITER //
CREATE TRIGGER Carperperson  /* This Trigger Blocks a customer from renting two cars on the same name twice on one day. */
BEFORE INSERT ON bookings   /*E.g. Mr.ABC cannot rent a Ford and a Nissan on the same day. Has to return first car first.[WORKING]*/
FOR EACH ROW 
BEGIN
IF EXISTS (
    SELECT 1
    FROM bookings 
    WHERE NEW.customer_id = bookings.customer_id
      AND ((new.start_date >= bookings.start_date 
            and new.start_date < bookings.end_date)
        or (new.end_date > bookings.start_date 
            and new.end_date < bookings.end_date))
) THEN
    SIGNAL SQLSTATE '45000' 
        set message_text='You can only book one car per single customer a day!' ; /* This triggers only allows to rent a car for 7 days, not more, not less[WORKING]*/
END IF;
        IF ( NEW.end_date > NEW.start_date   INTERVAL 7 DAY ) THEN
   SIGNAL SQLSTATE '45000'
   SET MESSAGE_TEXT = '7 is the maximum. Please choose an earlier date.'; /*The end_date is more than seven days after start_date*/

   END IF;   
 SET NEW.TheDuration = DATEDIFF(NEW.bookings.end_date, NEW.bookings.start_date)  -- -- TO CALCULATE DURATION BETWEEN 2 DATES
END;
//

DELIMITER ;
  

Проблема заключается внутри…

 SET NEW.TheDuration = DATEDIFF(NEW.bookings.end_date, NEW.bookings.start_date)  -- -- TO CALCULATE DURATION BETWEEN 2 DATES