CODEWITHSUNDEEP
mysqlvariablestriggers
David Santiago Iriarte Zamora
David Santiago Iriarte Zamora

Reputation: 163

how to declare variable in mysql trigger

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

Upvotes: 3

Views: 23334

Answers (1)

Robert Tirta
Robert Tirta

Reputation: 2911

 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

Upvotes: 4

Related Questions