how to declare variable in mysql trigger

Question

this is my trigger

CREATE TRIGGER proximo_pago 
AFTER INSERT ON pago FOR EACH ROW
BEGIN
    DECLARE max_orden integer;
    DECLARE num_lote =NEW.lote;
        BEGIN
            SET max_orden = (SELECT MAX(orden) FROM PAGO WHERE LOTE=num_lote);
            SELECT max_orden INTO : NEW.orden from dual;
            END
END

and the error

Error SQL query:

CREATE TRIGGER proximo_pago 
AFTER INSERT ON pago FOR EACH ROW
BEGIN
    DECLARE max_orden integer;

MySQL said: Documentation

#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

did you change the delimeter? if not, then the ; on your first declare is terminating the entire trigger definition. — Marc B
– Marc B, Commented May 29, 2015 at 15:52
You need to change the delimiter before defining the trigger. Try delimiter / and put that character also at the end of your trigger definition. — juergen d
– juergen d, Commented May 29, 2015 at 15:52

Robert Tirta · Accepted Answer · 2016-01-21 06:00:52Z

4

 DECLARE TEMPKODE VARCHAR(10);
 DECLARE TEMP VARCHAR(5);

or if you want to assign you can use

 DECLARE TEMP INT DEFAULT 6;

To assign a variable

myvariable := TEMPKODE;
SET myvariable := TEMPKODE;

Maybe what you mean is :

CREATE TRIGGER proximo_pago 
AFTER INSERT ON pago FOR EACH ROW
BEGIN
DECLARE max_orden INT;
DECLARE num_lote INT;
    BEGIN
        SET num_lote := NEW.lote;
        SELECT MAX(ordern) INTO max_ordern FROM PAGO WHERE LOTE = num_lote;
        SET NEW.ordern := max_ordern;

END

edited Jan 21, 2016 at 6:00

answered Jan 21, 2016 at 5:54

Robert Tirta

2,9193 gold badges23 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to declare variable in mysql trigger

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related