Error creating trigger in MySql

Question

I have an error creating this trigger in a MySql db:

CREATE TRIGGER AggiornaQuantitaLotto AFTER UPDATE ON Lotti
 FOR EACH ROW
 BEGIN
    DECLARE Differenza INT DEFAULT 0;

    IF OLD.Quantita <> NEW.Quantita THEN
        SET Differenza = NEW.Quantita - OLD.Quantita;
        
        UPDATE Giacenze
        SET Totale = Totale + Differenza,
            Disponibile = Disponibile + Differenza
        WHERE CodP = NEW.CodP;
    END IF;
END;

Where am I going wrong? the error message says:

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

Where is the line 4 is the 'DECLARE ...'

Thanks in advance

The problem is most likely that you need to specify delimiters; the answer to question I marked as duplicate shows you how to resolve it (as does the answer below too). I forgot that my duplicate flag would close the question immediately though. If the proposed solution doesn't work for you please leave a comment and I'll vote to reopen your question. — jpw, Oct 11 '15 at 18:25

score 2 · Accepted Answer · answered Oct 11 '15 at 18:20

You probably just need a delimiter statement (nothing else is obviously wrong):

DELIMITER $$

CREATE TRIGGER AggiornaQuantitaLotto AFTER UPDATE ON Lotti
 FOR EACH ROW
 BEGIN
    DECLARE Differenza INT DEFAULT 0;

    IF OLD.Quantita <> NEW.Quantita THEN
        SET Differenza = NEW.Quantita - OLD.Quantita;

        UPDATE Giacenze
            SET Totale = Totale + Differenza,
                Disponibile = Disponibile + Differenza
        WHERE CodP = NEW.CodP;
    END IF;
END;
$$
DELIMITER ;

Note: I would be tempted to write the logic like this:

 BEGIN
    UPDATE Giacenze g
        SET Totale = Totale + (NEW.Quantita - OLD.Quantita),
            Disponibile = Disponibile + (NEW.Quantita - OLD.Quantita)
    WHERE g.CodP = NEW.CodP AND NEW.Quantita <> OLD.Quantita
 END;

This just eliminates the if.

Error creating trigger in MySql

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

1 Answers1