CODEWITHSUNDEEP
mysqldatabasetriggers
user3784251
user3784251

Reputation: 520

#1413 error in mysql while creating a trigger

using mysql in phpmyadmin i started creating a trigger.. but i got this error

MySQL said: #1413 - Duplicate handler declared in the same block

I dont know whch is going wrong.. also i am creating trigger with three loops for the first time.. I am a beginner too.. Help me to solve this.. It may be a repeated question. I need to understand what is wrong with my trigger. this trigger will execute after inserting to the table external

My trigger contains

BEGIN
DECLARE scode varchar(30);
DECLARE grade varchar(30);
DECLARE val integer;
DECLARE credit integer;
DECLARE gval integer;
DECLARE temp double;
DECLARE cgpa decimal(4,3);
DECLARE t double;
DECLARE done int default false;
DECLARE done1 int default false;
DECLARE done2 int default false;
DECLARE cur1 CURSOR FOR SELECT Sub_Code,Grade FROM external where Reg_No=new.Reg_No;
DECLARE continue handler for not found set done=true;
DECLARE continue handler for not found set done1=true;
DECLARE continue handler for not found set done2=true;
SET credit=0;
OPEN cur1;
my_loop: loop
set done=false;
fetch cur1 into scode,grade;
if done then
leave my_loop;
end if;
DECLARE cur2 CURSOR FOR SELECT Credits FROM subj_mast WHERE Sub_Code=scode;
OPEN cur2;
my_loop1: loop
set done1=false;
fetch cur2 into val;
if done1 then
leave my_loop1;
end if;
set credit=credit+val;
DECLARE cur3 CURSOR FOR SELECT gvalue FROM gradevalue where Grade=grade;
OPEN cur3;
my_loop2: loop
set done2=false;
fetch cur3 into gval;
if done2 then
leave my_loop2;
end if;
set temp=val*gval;
set t=t+temp;
end loop my_loop;
end loop my_loop1;
end loop my_loop2;
set cgpa=t/credit;
close cur1;
close cur2;
close cur3;
insert into result values(new.Reg_No,cgpa);
END

this may be a too much code . but i need a clear answer.. Waiting for answers..

Upvotes: 2

Views: 2734

Answers (1)

Ravinder Reddy
Ravinder Reddy

Reputation: 23982

Error message is very clear that Duplicate handler declared in the same block.

When you have multiple cursors, you can make use of a single not found handler with proper care.

Option 1 Possibility:

  1. Looping through Cursor 1 should not call opening of either of Cursor 2 and Cursor 3.
  2. Then, on closing Cursor 1, reset the same done variable and use with next cursor.
  3. Open next cursor and follow above two steps.
  4. With this option possibility, you don't need to declare multiple same type handlers in the same block.

Option 2 Possibility:

In your code you are declaring other cursors while looping through previous cursors fetch results. That part is another error. You can't DECLARE wherever you want. All DECLARE statements must be on top of a BEGIN block. Hence, if you want to define another cursor based on fetch result of another cursor, use another BEGIN --- END block.

Change your trigger body as follows: 

BEGIN
    DECLARE scode varchar(30);
    DECLARE grade varchar(30);
    DECLARE val integer;
    DECLARE credit integer;
    DECLARE gval integer;
    DECLARE temp double;
    DECLARE cgpa decimal(4,3);
    DECLARE t double;

    DECLARE done int default false;
    DECLARE cur1 CURSOR FOR 
                 SELECT Sub_Code, Grade FROM external where Reg_No = new.Reg_No;
    DECLARE continue handler for not found set done = true;

    SET credit = 0;

    OPEN cur1;

    my_loop: loop
        fetch cur1 into scode, grade;
        if done then
            leave my_loop;
        end if;
        BEGIN
            DECLARE cur2 CURSOR FOR 
                         SELECT Credits FROM subj_mast WHERE Sub_Code = scode;
            DECLARE continue handler for not found set done1 = true;

            OPEN cur2;

            my_loop1: loop
                fetch cur2 into val;
                if done1 then
                    leave my_loop1;
                end if;

                set credit = credit + val;

                BEGIN
                    DECLARE cur3 CURSOR FOR 
                                 SELECT gvalue FROM gradevalue where Grade = grade;
                    DECLARE done2 int default false;

                    OPEN cur3;

                    my_loop2: loop
                        fetch cur3 into gval;
                        if done2 then
                            leave my_loop2;
                        end if;

                        set temp = val * gval;
                        set t = t + temp;
                    end loop my_loop2;

                    close cur3;
                END;
            end loop my_loop1;

            close cur2;
        END;
    end loop my_loop;

    close cur1;

    set cgpa = t / credit;

    insert into result values( new.Reg_No, cgpa );
END;

Document Reference:

DECLARE Syntax

DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements.

Declarations must follow a certain order. Cursor declarations must appear before handler declarations. Variable and condition declarations must appear before cursor or handler declarations

Upvotes: 6

Related Questions