Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2002
    Posts
    6

    Unanswered: "SELECT INTO" statements failing within trigger definations:

    Dear Sir, I was just wondering why this "SELECT INTO" statements (with a local variable ) failing within following trigger definations:

    create trigger fgg_assign_del
    after update on fgt_assignments
    REFERENCING OLD AS old FOR EACH ROW MODE DB2SQL
    begin atomic
    declare temp12 integer;
    select count (*) into temp12 from tpt_classes p;
    --where
    -- old.project_id = p.id and
    -- p.status >= 400 and p.status < 500;
    if (temp12 >= 1) then
    SIGNAL SQLSTATE '-20639' ('');
    end if;

    /* clean up all reservations */
    delete from fgt_events where related_to = old.id;
    end
    __________________________________________________ ___
    The error generated is as follows:
    DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:

    SQL0104N An unexpected token "temp12" was found following "elect count (*) into". Expected tokens may include: "<space>". LINE NUMBER=6.

    SQLSTATE=42601
    __________________________________________________ ___
    Well, basicaly i am translating oracle triggers to DB2 triggers so corresponding oracle trigger is running fine!

    Thks in advance!

  2. #2
    Join Date
    Mar 2002
    Posts
    17
    I have the same problem. I simulated these queries outside a trigger and they work fine but once in the trigger, NOT AT ALL.

  3. #3
    Join Date
    Apr 2002
    Posts
    6
    Well Musa I got a crude solution! We can do something like this :

    declare temp12 integer;
    set temp12 = select count (*) from tpt_classes p;

    that is we use "set" to get rid of the problem. Seems to be fine with me, if any intecracis do let me know.

    Thks a lott Musa!!
    Cheers!!
    Shamik.B

  4. #4
    Join Date
    Oct 2001
    Location
    Bangalore
    Posts
    186
    Hi,
    I have also tried the same thing but i do feel that DB2 does not allow the way shamik_bhat has done.

    >declare temp12 integer;
    >set temp12 = select count (*) from tpt_classes p;

    Can you please show the whole trigger where you say that it was successful.
    I have a doubt that it will really work.
    -Prashant
    Prashant

  5. #5
    Join Date
    Apr 2002
    Posts
    6
    Sure. Here is the code u have asked for!

    create trigger fgg_assign_del
    after update on fgt_assignments
    REFERENCING OLD AS old FOR EACH ROW MODE DB2SQL
    begin atomic
    declare temp integer;
    /* check references */

    /* can't delete assignments for an archived class */
    set temp = (select count (*) from tpt_classes p
    where old.project_id = p.id and p.status >= 400 and p.status < 500);
    if (temp >= 1) then
    SIGNAL SQLSTATE '-20639' ('');
    end if;

    /* clean up all reservations */
    delete from fgt_events where related_to = old.id;
    end@

    it's running fine in my case. I agree what if more than one to be selected. If it is one or 2 u can go for same style. CHECK IT OUT!
    BUT MY MAIN CONCERN REMAINS HOW TO USE CURSORS IN TRIGGER ...well we can use FOR LOOP but then select for update is real flavour ... no solutions from anyone till now ... can anyone please!!

    Thks GUYS!
    shamiik.B(SHAMIK_BHAT)

  6. #6
    Join Date
    Oct 2001
    Location
    Bangalore
    Posts
    186
    Hi ,
    Sorry to bother you .
    The trigger works, i had problems as there was a commit statement in the trigger and the triggers do not allow such statements.
    I will get back to your problem as soon as possible.
    Prashant G Dahalkar
    Prashant

  7. #7
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106

    Re: "SELECT INTO" statements failing within trigger definations:

    Even though this is posting is old, answering for those that search the forum for similar problem.

    Change SELECT INTO line to SET temp12 = (SELECT COUNT(*) FROM tpt_classes)


    Originally posted by shamik_bhat
    Dear Sir, I was just wondering why this "SELECT INTO" statements (with a local variable ) failing within following trigger definations:

    create trigger fgg_assign_del
    after update on fgt_assignments
    REFERENCING OLD AS old FOR EACH ROW MODE DB2SQL
    begin atomic
    declare temp12 integer;
    select count (*) into temp12 from tpt_classes p;
    --where
    -- old.project_id = p.id and
    -- p.status >= 400 and p.status < 500;
    if (temp12 >= 1) then
    SIGNAL SQLSTATE '-20639' ('');
    end if;

    /* clean up all reservations */
    delete from fgt_events where related_to = old.id;
    end
    __________________________________________________ ___
    The error generated is as follows:
    DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:

    SQL0104N An unexpected token "temp12" was found following "elect count (*) into". Expected tokens may include: "<space>". LINE NUMBER=6.

    SQLSTATE=42601
    __________________________________________________ ___
    Well, basicaly i am translating oracle triggers to DB2 triggers so corresponding oracle trigger is running fine!

    Thks in advance!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •