TamZSAUb

1ALTER TABLE bookings 
2ADD COLUMN TheDuration varchar(10); 
3     
4CREATE TRIGGER check_licence /*This trigger will approve customers with a valid licence */
5BEFORE UPDATE ON customers                 
6FOR EACH ROW 
7BEGIN 
8    SET NEW.Status = CASE WHEN NEW.valid_licence = 'Yes' 
9                          THEN 'Approved'
10                          ELSE 'Unapproved'  /*So if a Customer has a valid licence, He will be automatically approved. */
11                                        /*But if he doesn't he will become unapproved[WORKING]*/
12
13SET NEW.TheDuration = DATEDIFF(NEW.bookings.end_date, NEW.bookings.start_date)   -- -- TO CALCULATE DURATION BETWEEN 2 DATES
14END;
15//  
16DELIMITER ; 
17     
18SET NEW.TheDuration = DATEDIFF(NEW.bookings.end_date, NEW.bookings.start_date)   -- -- TO CALCULATE DURATION BETWEEN 2 DATES 
19     
20DELIMITER //
21CREATE TRIGGER Carperperson  /* This Trigger Blocks a customer from renting two cars on the same name twice on one day. */
22BEFORE 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]*/
23FOR EACH ROW 
24BEGIN
25IF EXISTS (
26    SELECT 1
27    FROM bookings 
28    WHERE NEW.customer_id = bookings.customer_id
29      AND ((new.start_date >= bookings.start_date 
30            and new.start_date < bookings.end_date)
31        or (new.end_date > bookings.start_date 
32            and new.end_date < bookings.end_date))
33) THEN
34    SIGNAL SQLSTATE '45000' 
35        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]*/
36END IF;
37        IF ( NEW.end_date > NEW.start_date + INTERVAL 7 DAY ) THEN
38   SIGNAL SQLSTATE '45000'
39   SET MESSAGE_TEXT = '7 is the maximum. Please choose an earlier date.'; /*The end_date is more than seven days after start_date*/
40
41   END IF;   
42 SET NEW.TheDuration = DATEDIFF(NEW.bookings.end_date, NEW.bookings.start_date)  -- -- TO CALCULATE DURATION BETWEEN 2 DATES
43END;
44//
45
46DELIMITER ; 
47     
48SET NEW.TheDuration = DATEDIFF(NEW.bookings.end_date, NEW.bookings.start_date)  -- -- TO CALCULATE DURATION BETWEEN 2 DATES