473,394 Members | 1,717 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,394 software developers and data experts.

HELP! Variable looses value in loop within trigger

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
3 2649
I've forotten:
DB2/LINUX
SQL08020

Nov 12 '05 #2
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: asd | last post by:
I need to find the value/index of the previously selected item of a select list. That is, when the user selects an item from the list and a certain condition elsewhere in the form is not met, I...
14
by: Crimsonwingz | last post by:
Need to calculate a sum based on a number of factors over a period of years. I can use formula ^x for some of it, but need totals to carry over in the sum and have only been able to do this thus...
6
by: Newbie | last post by:
hi all, in a webform's codebehind, i have a variable (ie: nTotal). and in UI i have a hidden htmlinput (ie: fld1). Q: how to assign the value of nTotal to now i want to assign the value of...
1
by: David | last post by:
Hi, I'm having trouble copying table data to new records. I have two tables as follows: *** Specifications (Table) specification_ID (field) LINKED product_ID (field) specification_header...
11
by: Huayang Xia | last post by:
What will the following piece of code print? (10 or 15) def testClosure(maxIndex) : def closureTest(): return maxIndex maxIndex += 5 return closureTest()
6
by: satish2112 | last post by:
how can i pass a variable's value from javascript to perl script? how can i receive that value in the perl script? please help me!!!
2
by: satish2112 | last post by:
how can i pass a variable's value from javascript to perl script? how can i receive that value in perl script? Please help me!!!
15
Markus
by: Markus | last post by:
What i want to do: Get urls from the database and echo them out into a multiple columned table i.e. 4 pictures per row (recently uploaded table) MY problem is: I have, in my MySQL database,...
1
by: rockchicx23 | last post by:
i have to write a program that is a guessing the number game. the user has to choose the number of games they want to play. i need help setting up the loop that will run the number of times the user...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.