469,356 Members | 1,884 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,356 developers. It's quick & easy.

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

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_ENQUIRYITM
AFTER UPDATE OF
..
..
SET v_change_notes_oid = NEXTVAL FOR JABS.CHANGE_NOTES_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_ENQUIRYITM". Information returned for the error
includes
SQLCODE "-348", SQLSTATE "428F9" and message tokens "NEXTVAL FOR
JABS.CHANGE_NOTES_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.
Nov 12 '05 #1
4 4456
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 =>
Nov 12 '05 #2
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_INFO() 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.
Nov 12 '05 #3
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
Nov 12 '05 #4
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.
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Darren | last post: by
reply views Thread by Yogesh | last post: by
3 posts views Thread by beer | last post: by
5 posts views Thread by Peter Erickson | last post: by
1 post views Thread by deepdata | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.