-1

I am working on a project of a car rental at the moment. I want to create a trigger that will calculate the number of days between two dates, and display it in a new column. So I realized I need to alter my bookings table because I don't have a duration table.

ALTER TABLE bookings
ADD duration varchar(10);

After altering, I think I need to create an update trigger because DATEDIFF function is used for querying. Could someone help me with this trigger? E.g. Count the amount of days between 10th March and 15th March - 5 days. Any help is appreciated. I assume it will be an UPDATE trigger??

0

You would require both an insert and an update trigger for this purpose.

CREATE TABLE bookings(booking_start_date date, booking_end_date date, duration int unsigned);

CREATE TRIGGER insert_bookings BEFORE INSERT ON bookings 
FOR EACH ROW SET NEW.duration = DATEDIFF(NEW.booking_end_date, NEW.booking_start_date);

CREATE TRIGGER update_bookings BEFORE update ON bookings
FOR EACH ROW SET NEW.duration = DATEDIFF(NEW.booking_end_date, NEW.booking_start_date);

INSERT INTO bookings(booking_start_date, booking_end_date) values("2019-03-25","2019-03-27");

UPDATE bookings set booking_start_date = "2019-03-24" where booking_start_date = "2019-03-25";

SELECT * FROM bookings;

However, if you are using MySQL 8.0+, you can make use of generated columns by defining your column duration as DATEDIFF(booking_end_date, booking_start_date).

  • Thanks for your help first of all. I'm not exactly sure how this trigger works. So... I have the table, You create 2 triggers Insert/update.. and then - Does the INSERT INTO go under the bookings table with rest of records? Why do you use dates in the update statement - is that when someone is querying it, right? So it's like an example query for each of the triggers? Thanks for the explanation with 8.0 SQL – Grubertusino Mar 26 at 23:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.