nextval, prevval and trigger

[Adrian]

I have created following sequence:

create sequence DB2ADMIN.MIEJ_SP_seq
START WITH 1
INCREMENT BY 1
NOMAXVALUE
NOCYCLE
NOCACHE
ORDER;

and table:

create table MIEJ_SP
( miej_sp char(1),
abcd varchar(10)
)

I want use the sequence in the before insert trigger:

CREATE TRIGGER DB2ADMIN.MIEJ_SP_BEF_INS1 NO CASCADE BEFORE INSERT ON
DB2ADMIN.MIEJ_SP REFERENCING NEW AS new FOR EACH ROW MODE DB2SQL
when(1=1)
begin atomic
if (new.miej_sp = '>')
then
set (new.miej_sp) =
(select cast( nextval for db2admin.miej_sp_seq as char) from
db2admin.simple);
end if;
end;

--(simple has one row and one column)

but I get an error:

SQL0348N "NEXTVAL FOR DB2ADMIN.MIEJ_SP_SEQ" cannot be specified in this
context. LINE NUMBER=1. SQLSTATE=428F9

In readme.txt file in sqllib directory it was described like this:

Examples of where NEXTVAL and PREVVAL expressions cannot be
specified (SQLSTATE 428F9) are:
+ join condition of a full outer join
+ DEFAULT value for a column in a CREATE TABLE or ALTER TABLE
statement
+ generated column definition in a CREATE TABLE or ALTER TABLE
statement
+ condition of a CHECK constraint
+ CREATE TRIGGER statement
+ CREATE VIEW statement
+ CREATE METHOD statement
+ CREATE FUNCTION statement.

Why propably the most useful thing of sequnces - using it within the
triggers etc. - is not supported?
Will this situation change by the future fixpacks?
How can automatically increment field be implement using sequences without
insert nextval and prevval directly into the insert statement? ( ...not
using identity columns)

Thanx.
Adrian
akal...@taran.com.pl

[Serge Rielau]

Hi Adrian,

Funny you say that... NEXTVAL was added to triggers very late (after V7.2 beta
was out I think). So the error message text is not accurate anymore.
Could it be you are on the V7.2 beta and not FP3 or V7.2 GA?

Note that nonetheless PREVVAL is currently not supported.

Here is the example that I tried on W2K DB2 V7 FP3:
db2 => create trigger trg2 no cascade before insert on t1 referencing new as n
f
or each row mode db2sql begin atomic if n.c1 is null then set n.c1 =
length(cast
(nextval for seq21 as char)); end if; end;
DB20000I The SQL command completed successfully.

Cheers
Serge

[lsu...@mb.sympatico.ca]

On Fri, 27 Jul 2001 12:48:01, Serge Rielau <sri...@ca.ibm.com> wrote:

> Hi Adrian,
>
> Funny you say that... NEXTVAL was added to triggers very late (after V7.2 beta
> was out I think). So the error message text is not accurate anymore.
> Could it be you are on the V7.2 beta and not FP3 or V7.2 GA?
>
> Note that nonetheless PREVVAL is currently not supported.
>

Of course PREVVAL is what I would like to have supported in before
triggers

(hint, hint)

<snip>

--
Lorne Sunley

[Serge Rielau]

Hi,

Which leads to the question: Why?
If you do a NEXTVAL in the before trigger you can assign it to a local variable.
So the only function you would gain is to use PREVVAL to get the last NEXTVAL from
some one outside of the trigger.
But you as the trigger developer have no influence on
what the application did prior to submitting the update
which fired the after trigger
which did the insert
which fired the before trigger
that did the PREVVAL....

(... I don't know why she swallowed the fly... I think she might die....;-)

OK, I gave an evil example, give me a good one :-)

Cheers
Serge

[Adrian]

Hi Serge!

> Could it be you are on the V7.2 beta and not FP3 or V7.2 GA?

aix, db2 v7 fp3

> Note that nonetheless PREVVAL is currently not supported.
>
> Here is the example that I tried on W2K DB2 V7 FP3:
> db2 => create trigger trg2 no cascade before insert on t1 referencing new
as n
> f
> or each row mode db2sql begin atomic if n.c1 is null then set n.c1 =
> length(cast
> (nextval for seq21 as char)); end if; end;
> DB20000I The SQL command completed successfully.

It really works.Thanks.

But why does it not work within select?

CREATE TRIGGER DB2ADMIN.MIEJ_SP_BEF_INS1 NO CASCADE BEFORE INSERT ON
DB2ADMIN.MIEJ_SP REFERENCING NEW AS new FOR EACH ROW MODE DB2SQL
when(1=1)
begin atomic
if (new.miej_sp = '>')
then
set (new.miej_sp) =

(select cast( nextval for db2admin.miej_sp_seq as char(1)) from
db2admin.simple);
end if;
end;

And it works without select...
...
cast(nextval for db2admin.miej_sp_seq as char(1))
...

Adrian

[Serge Rielau]

Hi Adrian,

The select on the right hand side is a subselect.NETXVAL isn't allowed in this
context. Granted, I don't see a reason why it should be restritced there.
The restriction protects DB2 from funny semantics in scalar subqueries (how
often does it get execututed or in predicates).
I suppose we can revisit these restrictions as soon as IBM agrees with Oracle
on the semantics in the SQL Standards. Until then we decided to restrict
everything that isn't really needed.

Cheers
Serge