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