0

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

5
  • DOUBLE(3,2) is rather nonsensical -- it can hold numbers between -9.99 and +9.99, with an extra rounding error. Did you mean DECIMAL(3,2) or did you mean DOUBLE? Or would FLOAT suffice?
    – Rick James
    Jan 16, 2016 at 0:56
  • odometer INT(5) is a lucky mistake. The (5) means nothing, INT can hold -2 billion to +2 billion. Suggest MEDIUMINT UNSIGNED.
    – Rick James
    Jan 16, 2016 at 0:57
  • How is "that appear in the department table that has a booking" coded?
    – Rick James
    Jan 16, 2016 at 1:02
  • 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. Also, all your references to bookings.<column> should be to NEW.<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. Jan 16, 2016 at 1:40
  • Sorry yes I did mean decimal, I have changed the int and double. Sorry that is another mistake, I meant just the table "department". There is not a junction table here as a booking can only be held by one faculty member. I wish the attribute "owed to tfbs" to be updated after "odemeter_end" is updated.
    – JackGroves
    Jan 16, 2016 at 1:45

1 Answer 1

0

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.

1
  • Thank you! Your syntax was pretty much perfect. I had considered using variables but couldn't get them to work. This is perfect and has helped me see why, thank you so much for your help!
    – JackGroves
    Jan 16, 2016 at 2:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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