By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,610 Members | 2,310 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,610 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Share this Question
Share on Google+
4 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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.