CODEWITHSUNDEEP
mysqlsqlsyntaxtriggers
rookiecookie
rookiecookie

Reputation: 199

How to decrement a value in another table via a trigger? (MySQL)

I want my trigger to decrement the quantity by 1 in the table "quantity" when a new row is added to "rented_equipment_log" where date_returned has a value of NULL.

A basic synopsis of the database is that there is an equipment table with columns; model, maker, type and quantity. (E.g. 1001, 'Jackson', 'oar', 10)

The table rented_equipment_log has the columns; member_id, model, type, date_taken, date_returned. (E.g. 17225663, 1001, oar, 2018-11-26, 2018-11-27)

So when a member takes out a piece of equipment but has not yet returned it (date_returned is null), the quantity of the same model decrements by 1 in the table equipment.

However I'm getting a syntax error. I've looked at other questions similar to this and still can't figure out what the error is.

Here's the trigger:

delimiter //
CREATE TRIGGER UpdateQuantity 
AFTER INSERT ON rented_equipment_log
FOR EACH ROW BEGIN
    DECLARE q integer;
    SELECT quantity INTO q FROM equipment WHERE model = NEW.model; 
    IF (date_returned IS NULL) THEN
    UPDATE equipment
    SET quantity = q -1 WHERE model = NEW.model
END IF;
END;//

And here's the error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END IF; END' at line 9

Upvotes: 0

Views: 835

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

I'm not sure why you are getting an error there. But your query is more complicated than necessary:

delimiter //

CREATE TRIGGER UpdateQuantity 
AFTER INSERT ON rented_equipment_log
FOR EACH ROW
BEGIN
    UPDATE equipment e
        SET e.quantity = e.quantity - 1
        WHERE e.model = NEW.model and ?.date_returned IS NULL;
END;//

I'm not sure if date_returned should be new.date_returned or e.date_returned. I don't even understand that condition. I would be expecting e.equipment > 0.

Upvotes: 3

Related Questions