MySQL Trigger to Update a Row in a Separate Table via Joins if Only One Row is Updated

MySQLtriggerupdate

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

Best Answer

Triggers are procedural, so my inclination would be to perform the join and update as separate steps; this IMHO would make for simpler and more readable DML.This code should do what you want:

DELIMITER $$

CREATE TRIGGER owed
    AFTER UPDATE ON bookings
    FOR EACH ROW
  BEGIN
    DECLARE dept VARCHAR(20);
    DECLARE rate DECIMAL(3, 2);
    IF NEW.odemeter_end <> OLD.odemeter_end THEN
      SELECT fm.department, vt.price
        INTO dept, rate
        FROM faculty_members fm, vehicles v, vehicle_types vt
        WHERE NEW.idMember = fm.idMember
          AND NEW.idVehicle = v.idVehicle
          AND v.type = vt.type;

      UPDATE departments
        SET owed_to_tfbs = (((NEW.odemeter_end - NEW.odemeter_start) * rate)
                           + owed_to_tfbs - NEW.fuel_purchased)
          WHERE department = dept;
    END IF;
  END;
$$

DELIMITER ;

Note that I haven't tried to compile this, so I make no promises regarding syntax errors, but the semantics are sound. Also, I've used a WHERE-clause join instead of a JOIN-clause join, as I'm not sure you can use the latter to join on NEW.

Related Question