CODEWITHSUNDEEP
mysql
anb90
anb90

Reputation: 73

Update trigger MySQL giving syntax error

I want to create trigger to make update on table when inserting rows to other table, but I get a syntax error for this:

CREATE TRIGGER quantity AFTER INSERT ON sale_items
FOR EACH ROW
BEGIN
update products set quantity = quantity -1 where id =(
SELECT product_id
FROM sale_items
ORDER BY id desc
LIMIT 1)
END;

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' at line 7

Upvotes: 0

Views: 107

Answers (2)

Aman Aggarwal
Aman Aggarwal

Reputation: 18479

Use proper Delimiter in your trigger , the correct code is:

DELIMITER //
CREATE TRIGGER quantity AFTER INSERT ON sale_items
    FOR EACH ROW
    BEGIN
    update products set quantity = quantity - 1
        where id = new.product_id ;
    END//
DELIMITER ;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270873

This seems like a silly trigger. Why are you fetching the last update id using a subquery? It should be available through new:

DELIMITER //
CREATE TRIGGER quantity AFTER INSERT ON sale_items
    FOR EACH ROW
    BEGIN
    update products
        set quantity = quantity - 1
        where id = new.product_id
    END//
DELIMITER ;

Upvotes: 2

Related Questions