MySQL Trigger – Update Quantity on Another Table

insertMySQLtriggerupdate

I am new to triggers but want to know how I can use one in my database.
I have created a table for a maintenance log, a parts inventory, and a parts_used junction table. When maintenance is carried out the parts and log id's a inserted into parts_used. I want to be able to take subtract 1 from the quantity in the inventory for that part.

Maintenance log

CREATE TABLE maintenance_log (
    idlog int  (4) AUTO_INCREMENT,
    idVehicle int (2),
    description VARCHAR (80),
    entry_date DATE(),
    release_date DATE,
    idMechanic int (2),
    FOREIGN KEY (idVehicle) REFERENCES vehicles(idVehicle),
    FOREIGN KEY (idMechanic) REFERENCES mechanics(idMechanic),
PRIMARY KEY (idlog)
);

Parts

    CREATE TABLE parts (
    idParts int(3) AUTO_INCREMENT,
    part_description VARCHAR (60),
    quantity int (2),
PRIMARY KEY (idParts)
);

parts_used

CREATE TABLE parts_used(
    idParts int (3),
    idlog int  (4),
CONSTRAINT PK_partUsed PRIMARY KEY
    (
        idParts,
        idlog
    ),
    FOREIGN KEY (idParts) REFERENCES parts(idParts),
    FOREIGN KEY (idlog) REFERENCES maintenance_log (idlog)
);

My current trigger just returns ERROR with no other message. This is what I am using.

CREATE TRIGGER subtract_quantity
AFTER INSERT ON parts_used FOR EACH ROW
BEGIN
           UPDATE parts
           SET parts.quantity = parts.quantity - 1
           WHERE parts.idParts = NEW.idParts
END

DELIMITER ;

Can anybody help or offer any advice?

Best Answer

You need to have DELIMITER directives both before and after the CREATE TRIGGER statement, you need to end individual simple statements with semicolons, and you need to end the complete CREATE TRIGGER with the delimiter specified in the first DELIMITER directive:

DELIMITER //

CREATE TRIGGER subtract_quantity
AFTER INSERT ON parts_used FOR EACH ROW
BEGIN
  UPDATE parts
    SET parts.quantity = parts.quantity - 1
    WHERE parts.idParts = NEW.idParts;
END
//

DELIMITER ;

However, in this case your entire trigger body is a single UPDATE statement, so you can omit both the BEGIN ... END statements and the DELIMITER directives:

CREATE TRIGGER subtract_quantity
AFTER INSERT ON parts_used FOR EACH ROW
  UPDATE parts
    SET parts.quantity = parts.quantity - 1
    WHERE parts.idParts = NEW.idParts;

(The DELIMITER directives allow the parser to distinguish between the end of a compound-statement trigger or procedure body and the ends of the individual simple statements it contains, and are only required by some MySQL clients.)

Related Question