1

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?

2
  • It looks like you have two accounts based on the account that tried to edit the question. You can request the accounts to be merged to fix it.
    – user507
    Jan 13, 2016 at 14:13
  • Check the permissions granted to the executor of the trigger. In particular, can he write to parts.
    – Rick James
    Jan 13, 2016 at 23:35

1 Answer 1

2

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.)

1
  • This worked perfectly thank you! I was confused by the use of delimiters but this is helpful. Thanks again
    – JackGroves
    Jan 14, 2016 at 8:39

Your Answer

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

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