By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,589 Members | 2,255 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,589 IT Pros & Developers. It's quick & easy.

HELP! Variable looses value in loop within trigger

P: n/a
The following seems to be a bug. The execution returns rows 1,2. It
should return 1,1. In fact, if I run the code within a stored procedure
alone (not in a trigger), the loop doesn't overwrite the value of y
(works well).

create table test (a integer)
@
create table debug1 (a integer)
@
create trigger test_1 after insert on test referencing new as ins for
each row mode db2sql
begin atomic
declare x smallint;
declare y smallint;

set y = ins.a;
for loop as
select y from sysibm.sysdummy1
union
select 2 as y from sysibm.sysdummy1
do
set x = loop.y;
insert into debug1 values (y);
end for;
end
@
insert into test values (1)
@
select * from debug1
@
drop trigger test_1
@
drop table test
@
drop table debug1
@

A
-----------
1
2 --> WRONG!

Thank you
(Serge, are you there?) :))

Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
I've forotten:
DB2/LINUX
SQL08020

Nov 12 '05 #2

P: n/a
Gustavo Randich wrote:
The following seems to be a bug. The execution returns rows 1,2. It
should return 1,1. In fact, if I run the code within a stored procedure
alone (not in a trigger), the loop doesn't overwrite the value of y
(works well). Well.. there obviously is an inconsistency here. But I claim the trigger
is right, making the procedure wrong.

Let me beef up the example a tad by labeling the comppund as "LOCAL" for
educational purposes.
create table test (a integer)
@
create table debug1 (a integer)
@
create trigger test_1 after insert on test referencing new as ins for
each row mode db2sql local: begin atomic declare x smallint;
declare y smallint;

set y = ins.a;
for loop as
select y from sysibm.sysdummy1
union
select 2 as y from sysibm.sysdummy1
do
set x = loop.y;
insert into debug1 values (y);
end for;
end
@
insert into test values (1)
@
select * from debug1
@
drop trigger test_1
@
drop table test
@
drop table debug1
@

A
-----------
1
2 --> WRONG! Let's go back to old fashioned varibale tables:
INS.A = 1
LOCAL.Y = INS.A = 1
LOCAL.X IS NULL
1. row:
LOOP.Y = LOCAL.Y = 1
LOCAL.Y = 1 (no change)
LOCAL.X = LOOP.Y = 1
2. row:
LOOP.Y = 2
LOCAL.X = LOOP.Y = 2

The magic question is: Who is Y?
There is a hierarchy here for scoping:
1. INS
2. LOCAL
3. LOOP

Note that "AS SELECT Y FROM.." is in the scope of LOCAL
(it produces LOOP)
while "INSERT.. VALUES(Y)" is in the scope LOOP.
So you insert LOOP.Y which clearly is (1), (2).

Now, when I first read this note I thought you are running into an known
APAR which deals with an inconsistency on FOR processing.
Let's replace your query with:
AS SELECT y FROM T WHERE x=z DO
The body of the FOR loop updates x which poses soem interesting
question: Does this affect the ongoing processing of the FOR loop?
The answer in triggers is (wrongly): Yes. In procedures it's (rightly): No.
If that's what you _really_ run into (assuming you presented a
simplified exampel here) teh work around is to "back up" the changed
variable and refer to it in the AS SELECT ..... DO.
If this is what you run into, please call support.
Thank you
(Serge, are you there?) :))

No I'm not. I'm in Vienna at the DB2 conference. :-)

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #3

P: n/a
OK, I'll make a backup of the variable before the loop and use it in
the SELECT.

But, since I'm making an auto-translator of Informix to DB2 code, and I
can't leave this complex scenario to programmers (the directive is to
automate detection of possible failures), I've put a warning in the
output of the translator when the following condition occurs:

1. You are in a trigger
2. You are a loop SELECT
3. The SELECT has UNION
4. You are using a variable in the SELECT-list

Am I right?

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.