DECLARE not working in MYSQL TRIGGER

Question

this is my first trigger and my very first pl/sql code.

the variable declaration in the following trigger is not working and I can not check for the other errors.

[CREATE TRIGGER insert_rollno
BEFORE INSERT ON exam_candidate
FOR EACH ROW

BEGIN
    DECLARE max_num INTEGER(4);
    DECLARE apply INTEGER(10);
    DECLARE reg_yr VARCHAR(2);

    @reg_yr:=SUBSTRING(NEW.regno,12,2);
    SELECT MAX(num)
    INTO max_num
    FROM exam_candidate
    WHERE course_id=NEW.course_id
    AND semester=NEW.semester
    AND yr=NEW.yr;

    SELECT MAX(application)
    INTO apply
    FROM exam_candidate;

    @max_num:=@max_num+1;
    @apply:=@apply+1;

    INSERT INTO exam_candidate(rollno,num,application,regno,course_id,semester,yr,enrollment_dt)
    VALUES(CONCAT(reg_yr,'/',NEW.course_id,'/',CHAR(NEW.yr),CHAR(NEW.semester),CHAR(max_num)),max_num,apply,NEW.regno,NEW.course_id,NEW.semester,NEW.yr,SYSDATE());
END][1]

please help me solving this problem.

score 0 · Answer 1 · edited May 23 '17 at 11:58

You are mixing the user defined variables (starting with @) and the variables declared within a compound statement (using declare between begin and end). max_num and @max_num variables are two different ones. Rewrite your code to use variable names without @.

Also see MySQL: @variable vs. variable. Whats the difference? SO topic for more detailed explanation on the differences between the 2 types of variables.

score 0 · Answer 2 · edited Jul 18 '16 at 10:05

CREATE TRIGGER insert_rollno
BEFORE INSERT ON exam_candidate
FOR EACH ROW

BEGIN
    DECLARE max_num INTEGER(4);
    DECLARE apply INTEGER(10);
    DECLARE reg_yr VARCHAR(2);

    reg_yr:=SUBSTRING(NEW.regno,12,2);
    SELECT MAX(num)
    INTO max_num
    FROM exam_candidate
    WHERE course_id=NEW.course_id
    AND semester=NEW.semester
    AND yr=NEW.yr;

    SELECT MAX(application)
    INTO apply
    FROM exam_candidate;

    max_num:=max_num+1;
    apply:= apply+1;

    INSERT INTO exam_candidate(rollno,num,application,regno,course_id,semester,yr,enrollment_dt)
    VALUES(CONCAT(reg_yr,'/',NEW.course_id,'/',CHAR(NEW.yr),CHAR(NEW.semester),CHAR(max_num)),max_num,apply,NEW.regno,NEW.course_id,NEW.semester,NEW.yr,SYSDATE());
END

I think that this should work, because the variable with @ that u declared on the Declare block arent the same as those you used on your selects.Tell me if this works.

score 0 · Answer 3 · answered Nov 20 '20 at 12:37

0

for me adding this on top of the trigger declaration fixed the problem

DELIMITER $$

answered Nov 20 '20 at 12:37

abdeslam chettout

73
6

DECLARE not working in MYSQL TRIGGER

3 Answers3