I am trying to create a trigger so that when a column in a table called bookings is updated it will update a a calculated price value in table departments. I currently have my tables set up as so
CREATE TABLE bookings (
idBookings int(4) AUTO_INCREMENT,
idMember int(4),
departure_date DATE,
idVehicle int (2),
location VARCHAR(45),
checkout_signed_faculty BOOLEAN,
checkout_signed_tfbs BOOLEAN,
odemeter_start int(5),
odemeter_end int(5),
maintenance_needed BOOLEAN,
fuel_purchased DOUBLE(3,2) NOT NULL,
card_number BIGINT(16),
FOREIGN KEY (idMember) REFERENCES faculty_members(idMember),
FOREIGN KEY (idVehicle) REFERENCES vehicles(idVehicle),
FOREIGN KEY (location) REFERENCES locations(location),
PRIMARY KEY (idBookings)
);
CREATE TABLE vehicle_type (
type VARCHAR (20),
price DECIMAL(3,2)
PRIMARY KEY (type)
);
CREATE TABLE vehicles (
idVehicle int (2) AUTO_INCREMENT,
type VARCHAR (20),
registration VARCHAR(7),
price DOUBLE(2,2),
PRIMARY KEY (idVehicle)
);
CREATE TABLE departments (
department VARCHAR (20) NOT NULL,
owed_to_tfbs DECIMAL(3,2) NOT NULL,
PRIMARY KEY (department)
);
CREATE TABLE faculty_members (
idMember int (4) AUTO_INCREMENT,
member_name VARCHAR(45) NOT NULL,
department VARCHAR(20),
PRIMARY KEY (idMember),
FOREIGN KEY (department) REFERENCES departments(department)
);
And my trigger contains a lot of joins (I am unsure whether this is because of a possible flaw in the design) but is as so...
DELIMITER $$
CREATE TRIGGER owed AFTER UPDATE ON bookings FOR EACH ROW
IF NEW.odemeter_end <> OLD.odemeter_end THEN
UPDATE departments
left join faculty_members on departments.department = faculty_members.department
inner JOIN bookings on faculty_members.idMember = bookings.idMember
inner JOIN vehicles on vehicles.idVehicle = vehicles.idVehicle
inner JOIN vehicle_type on vehicles.type = vehicle_type.type
SET departments.owed_to_tfbs = ((((NEW.odemeter_end - NEW.odemeter_start)
* vehicle_type.price)
+ departments.owed_to_tfbs) - bookings.fuel_purchased)
WHERE NEW.odemeter_end <> OLD.odemeter_end;
END IF
$$
While the calculation is correct it is applying it to all the owed columns that appear in the department table that has a booking. I am unsure as to how to only apply this to only the updated rows Can any body help or offer any advice? I so far cannot find anything that works
DOUBLE(3,2)
is rather nonsensical -- it can hold numbers between -9.99 and +9.99, with an extra rounding error. Did you meanDECIMAL(3,2)
or did you meanDOUBLE
? Or wouldFLOAT
suffice?odometer INT(5)
is a lucky mistake. The(5)
means nothing,INT
can hold -2 billion to +2 billion. SuggestMEDIUMINT UNSIGNED
.bookings.<column>
should be toNEW.<column>
; you should never try to directly access the triggering tables columns from a row trigger. I'll have a complete solution in a few minutes.