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!

I have the same problem. I simulated these queries outside a trigger and they work fine but once in the trigger, NOT AT ALL.

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

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

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)

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

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)