SQL Triggers And Procedures For Rental Database
Trigger 1: Check and Update Customer’s Member Date
1) When new rows in the corresponding tables are entered the following data needs to be checked to make sure that the date is correct (ie either before or after the current date) Customer’s MemberDate: must be before or equal to the current date
Trigger:
DROP TRIGGER IF EXISTS Trigg_CustMemDate;
DELIMITER $$
CREATE TRIGGER Trigg_CustMemDate BEFORE INSERT ON customer
FOR EACH ROW
BEGIN
IF NEW.MemberDate > CURDATE() THEN
UPDATE customer SET NEW.MemberDate=CURDATE() WHERE CusomerID=NEW.CustomerID;
END IF;
END$$
DELIMITER ;
Note:
If Member date is after the current date, it automatically put the current date
Vehicle’s PurchaseDate: must be before or equal to the current date Trigger:
DROP TRIGGER IF EXISTS Trigg_VehPurDate;
DELIMITER $$
CREATE TRIGGER Trigg_VehPurDate BEFORE INSERT ON vehicle
FOR EACH ROW
BEGIN
IF NEW.PurchaseDate> CURDATE() THEN
UPDATE vehicle SET NEW.PurchaseDate=CURDATE() WHERE Rego=NEW.Rego;
END IF;
END$$
DELIMITER ;
Note:
If Purchase date is after the current date, it automatically put the current date
Rental’s CheckoutDate: must be after or equal to the current date Trigger:
DROP TRIGGER IF EXISTS Trigg_RenChkOutDate;
DELIMITER $$
CREATE TRIGGER Trigg_RenChkOutDate BEFORE INSERT ON rental
FOR EACH ROW
BEGIN
IF NEW. Checkout< CURDATE() THEN
UPDATE rental SET NEW. Checkout=CURDATE() WHERE RentalID=NEW.RentalID;
END IF;
END$$
DELIMITER ;
Note:
If Checkout date is before the current date, it automatically put the current date
2) When a new car is entered into the database, the value of the NextUpdate column should automatically be set to one month from the date the vehicle was added to the table.
Trigger:
DROP TRIGGER IF EXISTS Car_NextUpdate;
DELIMITER $$
CREATE TRIGGER Car_NextUpdate BEFORE INSERT ON vehicle
FOR EACH ROW
BEGIN
UPDATE vehicle SET NEW.NextUpdate=DATE_ADD(NEW.PurchaseDate, INTERVAL 30 DAY) WHERE Rego=NEW.Rego;
END$$
DELIMITER ;
3) The value of a vehicle will depreciate monthly. When the date of the NextUpdate for each vehicle arrives, the value of the vehicle should depreciate by 2.5% monthly. When the value has been updated, the date of the NextUpdate column of the Vehicle table should be set to one month from the date it was updated. (Don’t worry about situations where the date has been missed – assume that the system will be turned on every day, without fail)
DROP PROCEDURE IF EXISTS UpdateVehicleValue;
DELIMITER $$
CREATE PROCEDURE UpdateVehicleValue ()
BEGIN
DECLARE rego VARCHAR(6);
declare val INT(6);
declare nupdate Date;
DECLARE vehicle_cur CURSOR FOR SELECT Rego,VALUE,NextUpdate FROM vehicle;
Trigger 2: Set Next Update Date for New Cars
OPEN vehicle_cur;
loop_vehicle: LOOP
FETCH vehicle_cur INTO rego,val,nupdate;
IF done THEN
LEAVE loop_vehicle;
END IF;
IF nupdate=CURDATE() THEN
UPDATE vehicle SET VALUE = VALUE – VALUE * 2.5/100 WHERE REGO=rego;
UPDATE vehicle
SET
NextUpdate = DATE_ADD(NextUpdate, INTERVAL 30 DAY)
WHERE
REGO = rego;
END IF;
END LOOP loop_vehicle;
CLOSE vehicle_cur;
END$$
DELIMITER ;
4) When a vehicle is rented (ie when a row is added to the rental table), the database should do the following:
- a) Automatically calculate the duedate depending on how many days the vehicle is rented out for.
- b) Automatically calculate the rent to be charged and input it into the rentalcharge table.
The rental of a car is calculated as follows:
(car type charge + 0.1% of the value of the car) per day + any insurance
DROP TRIGGER IF EXISTS Trigg_RentVehicle;
DELIMITER $$
CREATE TRIGGER Trigg_RentVehicle BEFORE INSERT ON rental
FOR EACH ROW
BEGIN
DECLARE carTypeCharge DECIMAL(6,2);
DECLARE insCharge DECIMAL(6,2);
DECLARE valueVehicle DECIMAL(6,2);
DECLARE rentamt DECIMAL(6,2);
SET NEW.Duedate=DATE_ADD(NEW.Checkin, INTERVAL RentDays DAY);
SELECT Charge INTO carTypeCharge FROM VehicleType WHERE VehicleTypeID = (SELECT VehicleTypeID FROM Rental WHERE RentalID=NEW.RentalID);
SELECT Price INTO insCharge FROM Insurance WHERE InsuranceID = (SELECT InsuranceID FROM Rental WHERE RentalID=NEW.RentalID);
SELECT VALUE INTO valueVehicle FROM Vehicle WHERE Rego = (SELECT Rego FROM Rental WHERE RentalID=NEW.RentalID);
SET rentamt= (carTypeCharge+ 0.1*valueVehicle)/NEW.RentDays + insCharge;
INSERT INTO RentalCharge (RentalID,Rent) VALUE (NEW.RentalID,rentamt);
END$$
DELIMITER ;
- c) If any accessories are rented (ie new rows added to the rentaccessory table), the quantity of that accessory (in the accessory table) should be reduced by the quantity taken. Also, the total charge of the accessories rented should be added to the rental charge previously calculated in (b) above.
DROP TRIGGER IF EXISTS Trigg_AccessRental;
DELIMITER $$
CREATE TRIGGER Trigg_AccessRental BEFORE INSERT ON RentAccessory
FOR EACH ROW
BEGIN
DECLARE acctotcost DECIMAL(6,2);
UPDATE Accessory SET Qty=Qty-NEW.Qty WHERE AccessoryID=NEW.AccessoryID;
SELECT Cost * NEW.Qty INTO acctotcost FROM Accessory;
UPDATE Rental SET RentalCharge=RentalCharge+acctotcost WHERE RentalID=NEW.RentalID;
END$$
DELIMITER ;
5) When the vehicle is returned (ie when the checkin date – which was originally NULL – is updated for that rental), the following should be done:
- a) Values for the fields OdometerIn, FuelLevel, Damage must be entered and therefore cannot be NULL when the car is returned.
- b) The number entered for the OdometerIn in the rental table should automatically be updated for that vehicle in the vehicle table.
- c) If the fuel tank is not full (ie, the level is not 1 but: 0.75, 0.5, 0.25, 0) the customer is charged for a tank fill up. (The cost that is charged is the (1-fuellevel that is entered) * fueltank (available in the vehicle table) * the currentcost (found in the fueltype table)). Once calculated this charge should be entered in the rentalcharge table.
- d) If the vehicle is not returned by the duedate a penalty is incurred (which is calculated as the rent charge per day plus a penalty. The penalty will differ depending on the number of days overdue).
DROP TRIGGER IF EXISTS Trigg_VehicleRental;
DELIMITER $$
CREATE TRIGGER Trigg_VehicleRental BEFORE UPDATE ON Rental
FOR EACH ROW
BEGIN
DECLARE fuelcost DECIMAL(6,2);
DECLARE ftank DECIMAL(6,2);
DECLARE totcost DECIMAL(6,2);
DECLARE penamt DECIMAL(6,2);
DECLARE rcharge DECIMAL(6,2);
DECLARE odue DECIMAL(6,2);
DECLARE insID VARCHAR(1);
IF NEW.Checkin IS NOT NULL THEN
IF NEW.OdometerIn IS NOT NULL AND NEW.FuelLevel IS NOT NULL AND NEW.Damage IS NOT NULL THEN
UPDATE Vehicle SET Odometer=NEW.OdometerIn WHERE Rego=NEW.Rego;
IF (NEW.FuelLevel<1) THEN
SELECT CurrentCost INTO fuelcost FROM FuelType WHERE FuelTypeID = (SELECT FuelTypeID FROM vehicle WHERE Rego = NEW.Rego);
SELECT FuelTank INTO ftank FROM vehicle WHERE Rego = NEW.Rego;
Procedure: Update Vehicle Value and Next Update Date
SET totcost=(1-FuelLevel) * ftank * fuelcost ;
SET rcharge=rcharge+totcost;
UPDATE rentalcharge SET Rent=rcharge WHERE RentalID=NEW.RentalID;
END IF;
IF Checkin > Duedate THEN
SELECT Rent, Overdue INTO rcharge, odue FROM rentalcharge WHERE RentalID = NEW.RentalID;
SET penamt=RentDays * Rent+odue * (Checkin – Duedate) ;
INSERT INTO Penalty (PenaltyType, MinDay, MaxDay, Cost) VALUES (‘Penalty8’,1,RentDays,penamt);
END IF;
END IF;
END IF;
END$$
DELIMITER ;
- e) If there are damages to the car, and the customer didn’t take the full insurance option, the damages are paid by the customer. If Partial Insurance is taken, the customer pays for half of the damages. The damages is calculated when a value is entered in the damage field of the rentalcharge table.
DELIMITER $$
CREATE PROCEDURE FindDamageCost (IN RentalID INT, OUT DamCost DECIMAL)
BEGIN
DECLARE Damage VARCHAR(1);
DECLARE InsuranceID VARCHAR(1);
DECLARE rental_cur CURSOR FOR SELECT * FROM Rental WHERE [email protected];
OPEN rental_cur;
SET DamageAmt=0;
loop_rental: LOOP
FETCH rental_cur INTO Damage, InsuranceID ;
IF done THEN
LEAVE loop_rental;
END IF;
SELECT Damage INTO DamageAmt FROM RentalCharge WHERE RentalID=RentalID;
IF Damage = ‘Y’ THEN
IF InsuranceID=’F’ THEN
UPDATE RentalCharge SET DamageAmt=DamageAmt WHERE RentalID=RentalID;
ELSE
UPDATE RentalCharge SET DamageAmt=DamageAmt/2 WHERE RentalID=RentalID;
END IF;
END IF;
END LOOP loop_rental;
CLOSE rental_cur;
END$$
DELIMITER ;
- f) If any accessories were rented, the quantity for that accessory should be increased by 1.
DROP TRIGGER IF EXISTS Trigg_VehicleAccess;
DELIMITER $$
CREATE TRIGGER Trigg_VehicleAccess BEFORE INSERT ON RentAccessory
FOR EACH ROW
BEGIN
UPDATE Accessory SET Qty=Qty+1 WHERE AccessoryID=NEW.AccessoryID;
END$$
DELIMITER ;
7) Old rentals – 6 months from the checkin date should be deleted from the rental table.
DELIMITER $$
CREATE PROCEDURE DeleteOldRental ()
BEGIN
DELETE FROM Rental WHERE TIMESTAMPDIFF(MONTH,Checkin,CURDATE())>=6;
END$$
DELIMITER ;
8) 12 month old vehicles (from date of purchase) will no longer be available for rent, and will be put up for sale. The database should automatically remove the old vehicle from the vehicle table and add it to the sale table. The data that is to be entered is similar to what is in the vehicle table, except for a slight variation: the FuelType, EngineCapacity and VehicleType will be added to the description field. (The FuelType and VehicleType should be the actual type not the number and the EngineCapacity should have the word ‘litre’ after the value)
DELIMITER $$
CREATE PROCEDURE TransferOldVehicle ()
BEGIN
DECLARE descrip VARCHAR(150);
DECLARE fueltype VARCHAR(50);
DECLARE vehtype VARCHAR(50);
DECLARE engcap VARCHAR(50);
DECLARE Rego VARCHAR(6);
DECLARE Description VARCHAR(60);
DECLARE Make VARCHAR(15);
DECLARE Model VARCHAR(15);
DECLARE Year INT(4);
DECLARE EngineCapacity DECIMAL(2,1);
DECLARE FuelTank INT(3);
DECLARE odometer INT(6);
DECLARE vehicle_cur CURSOR FOR SELECT Rego,Description,Make,Model,Year,VALUE,EngineCapacity,FuelTank,odometer FROM vehicle;
OPEN vehicle_cur;
loop_vehicle: LOOP
FETCH vehicle_cur INTO Rego,Description,Make,Model,Year,VALUE,EngineCapacity,FuelTank,odometer ;
IF done THEN
LEAVE loop_vehicle;
END IF;
IF checkin IS NOT NULL THEN
IF TIMESTAMPDIFF(MONTH,PurchaseDate,CURDATE())>=12 THEN
SELECT FuelType INTO fueltype FROM FuelType WHERE FuelTypeID = (SELECT FuelTypeID FROM Vehicle WHERE Rego=Rego);
SELECT VehicleType INTO vehtype FROM VehicleType WHERE VehicleTypeID = (SELECT VehicleTypeID FROM Vehicle WHERE Rego=Rego);
SET descrip=CONCAT(descrip,’,’,fueltype);
SET descrip=CONCAT(descrip,’,’,vehtype);
SET descrip=CONCAT(descrip,’,’,EngineCapacity,’,litre’);
INSERT INTO Sale (Rego, Description, Make, Model, Year, Odometer, Price) VALUES (Rego, descrip,Make,Model,Year,odometer,VALUE);
DELETE FROM vehicle WHERE Rego=Rego;
END IF;
END IF;
END LOOP loop_vehicle;
CLOSE vehicle_cur;
END$$
DELIMITER ;