V8.2 Trigger - NEXTVAL FOR seq -- behaviour change?

[Paul Reddin]

Hi,

Having just tested our database on V8.2 we get the following apparent
incompatibility .

A Trigger conatains the following line

CREATE TRIGGER JABS.AU_CHNG_EN QUIRYITM
AFTER UPDATE OF
..
..
SET v_change_notes_ oid = NEXTVAL FOR JABS.CHANGE_NOT ES_OID;
..

The trigger still CREATEs OK, but always now gives the following
runtime error
after an update?

SQL0723N An error occurred in a triggered SQL statement in trigger
"JABS.AU_CHNG_E NQUIRYITM". Information returned for the error
includes
SQLCODE "-348", SQLSTATE "428F9" and message tokens "NEXTVAL FOR
JABS.CHANGE_NOT ES_OID". SQLSTATE=09000

Do we need to change how this is coded or is this a new V8.2 Bug
(we were on V8.1.6 before) ?

Thanks.

Paul.

[Serge Rielau]

I can't repro.. can you provide a full script to repro?

db2 => connect to test;

Database Connection Information

Database server = DB2/NT 8.2.0
SQL authorization ID = SRIELAU
Local database alias = TEST

db2 => create table ttt(c1 int);
DB20000I The SQL command completed successfully.
db2 => create sequence s1;
DB20000I The SQL command completed successfully.
db2 => create trigger trg1 after update on ttt for each row begin atomic
db2 (cont.) => declare a int; --
db2 (cont.) => set a = nextval for s1; --
db2 (cont.) => end;
DB20000I The SQL command completed successfully.
db2 => insert into ttt values 1;
DB20000I The SQL command completed successfully.
db2 => update ttt set c1 = c1 + 1;
DB20000I The SQL command completed successfully.
db2 =>

[Paul Reddin]

Hi,

I have tried to workaround this problem by doing the following.

I have taken the trigger body and created a Stored Procedure and now
simply CALL the SP from the trigger.
- I wanted to do this anyway for performance, i.e the trigger body
was causing INSERTS and UPDATES to take several seconds to
compile.
and I've been waiting for V8.2 before doing this.

However, this seems to have thrown up another (bigger) issue which I
was hoping wouldn't be the case when calling SPs from triggers.

The inserts performed by the statements in the SP (identical to how
they
were in the Trigger) now cause a SQLCODE -746 !!!
i.e It is not allowed to read the original table that fired the
trigger from the SP. Not that I am explictly doing this, but the
table I am inserting into has an RI FK constraint back onto the
original table.

So, my general question is ...

It appears that the new CALL SP from a trigger, doesn't give the
statements in the SP the same 'context' as they have when they are in
the trigger body ?

Is this intended behaviour, or is there an option etc I can set so
that the SP
statements perform exactly as if they were in the trigger body?
- NB. I have tried coding them static & dynamic in the SP with no
difference.

(Also, it appears that a SNAPSHOT_APPL_I NFO() returns NULL from within
the called SP too - which it doesn't if the SP is called directly
rather than via
the trigger?)

Thanks.

Paul.

[Serge Rielau]

Paul,

See my very recent post on that topic.
DB2 wrt. the runtime error you observe is working as designed.
I'm currently canvasing feedback on whether it is working as desired :-)

Cheers
Serge

[Alexander Pope]

Serge Rielau <sr*****@ca.ibm .com> wrote in message news:<2t******* ******@uni-berlin.de>...

Paul,

See my very recent post on that topic.
DB2 wrt. the runtime error you observe is working as designed.
I'm currently canvasing feedback on whether it is working as desired :-)

Cheers
Serge

I believe my trigger/sp issue is the same/similar to Paul's.
I get the same -746 error. It appears the behaviour of the sql
statments in the trigger are dissimilar the behaviour of the sql
statments in the procedure.
I have abandoned the proc and have proceeded with the trigger until I
can figure out the problem.