Hello

We are having some problems with triggers, sequences and union all in
V8 on code that worked fine in V7. Was wondering if someone else has
seen this and/or knows what to do.

A trigger that runs after insert on, where the insert uses nextval on a
sequence for the key and the trigger uses union all we get this
message:

SQL0723N An error occurred in a triggered SQL statement in trigger
"TMP.T".
Information returned for the error includes SQLCODE "-348", SQLSTATE
"428F9"
and message tokens "NEXTVAL FOR TMP.AA". SQLSTATE=09000

Adding a full example below.

At first I though it was the reference we did to "newrow.id" (set by
nextval) that failed, but see the last trigger that fails with no
direct reference to the input data.

Tested with DB2 v8 (fp7 and fp10) on fedora core 3 and 4 (unsupported,
I know - plan on downloading the v8 windows trial version and see if
that does the same unless someone else runs this test for me ... hint,
hint

/Urban


--
-- Silly example to show a problem when moving from V7 to V8
--

-- create database apa;

connect to apa;

set schema tmp;

drop trigger T;
drop sequence aa restrict;
drop table aa;
drop table aa2;


create table aa (
id integer not null,
val integer default 0,

primary key (id)
);

create table aa2 (
id integer not null,
val integer default 0
);

create sequence aa;

insert into aa(id, val) values (nextval for aa, 1);
insert into aa(id, val) values (nextval for aa, 2);
insert into aa(id, val) values (nextval for aa, 3);

--
-- this trigger works
--
create trigger T
after insert on aa
referencing new as newrow
for each row mode db2sql
begin atomic
insert into aa2(id, val)
select newrow.id, 22 from aa; --
end;
drop trigger T;

--
-- the values() part fails in V8 but works in V7
--
create trigger T
after insert on aa
referencing new as newrow
for each row mode db2sql
begin atomic
insert into aa2(id, val)
select newrow.id, 33 from aa
union all
values (newrow.id, -1); --
end;
drop trigger T;

--
-- also fails
--
create trigger T
after insert on aa
referencing new as newrow
for each row mode db2sql
begin atomic
insert into aa2(id, val)
select id, 44 from aa
union all
select newrow.id, -1 from sysibm.sysdummy1; --
end;
drop trigger T;

--
-- also fails
--
create trigger T
after insert on aa
for each row mode db2sql
begin atomic
insert into aa2(id, val)
values (2, 2)
union all
values (1, 1); --
end;


insert into aa(id, val) values (nextval for aa, 4);
insert into aa(id, val) values (nextval for aa, 5);
insert into aa(id, val) values (nextval for aa, 6);

-- works
insert into aa(id, val) values (99, 4);