Error Handling in Triggers

Michael Kaplan

Error Handling in Triggers
Hello, List

Can anybode explain the reason to not passing SQLCODE 100 in the following
simple trigger ?

CREATE TRIGGER KAPSHD1D.UPD_STAT
AFTER INSERT ON KAPSHD1D.REQST_STAT
REFERENCING new as new
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
UPDATE KAPSHD1D.REQST_DTL SET REQ_STATUS = NEW.CUR_STATUS
WHERE REQ_NUM = NEW.REQ_NUM;
END

So, when UPDATE gets SQLCODE 100, the original INSERT into
the table REQST_STAT is finished with SQLCODE 0, SQLSTATE 00000, etc.

Actually, it is stated in manual that the warnings are not returned, but
is it correct in this case ? I do not want to create RI or use another
( BEFORE) trigger to get rid. It seems stupid to ask for existance and
spend more CPU.

We are DB2 ver 7 for OS/390 ver 2.10.

Thanks,

Michael Kaplan
DBA DB2
LEUMI CARD
Tel : 972-3-6177038

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Walter Janißen

Re: Error Handling in Triggers
(in response to Michael Kaplan)
Michael

How should the SQLCODE be passed? Should the insert get a +100. That will
be strange, because the insert will never get a +100.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Michael Kaplan

Re: Error Handling in Triggers
(in response to Walter Janißen)
Walter,

I would expect something like :

SQLCODE -723 with INFORMATION RETURNED: SQLCODE: +100, SQLSTATE: 02000

Of course, -723 can be replaced by some positive sqlcode

Thanks,
Michael Kaplan


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Walter Jani?en
Sent: Tuesday, January 11, 2005 1:08 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Error Handling in Triggers

Michael

How should the SQLCODE be passed? Should the insert get a +100. That will
be strange, because the insert will never get a +100.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Michael Kaplan

Re: Error Handling in Triggers
(in response to Michael Kaplan)
Walter,

I would expect something like :
SQLCODE -723 with INFORMATION RETURNED: SQLCODE: +100, SQLSTATE: 02000,

Of course, -723 can be replaced by some positive sqlcode.

Thanks,
Michael Kaplan

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Walter Jani?en
Sent: Tuesday, January 11, 2005 1:08 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Error Handling in Triggers

Michael

How should the SQLCODE be passed? Should the insert get a +100. That will
be strange, because the insert will never get a +100.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm