trigger for info instead of error

Michael Shields

trigger for info instead of error
I have created the following trigger:

CREATE TRIGGER test.OSG_ISRT
AFTER INSERT ON test.OSG
REFERENCING NEW AS NEW_OSG
FOR EACH ROW MODE DB2SQL
WHEN (1 <
(
SELECT COUNT(*) FROM ADRDNST.ORD_SHP_GRP
WHERE col1 = NEW_OSG.col1
AND col2 = NEW_OSG.col2
AND col3 = NEW_OSG.col3
AND col4 = NEW_OSG.col4
)
)
BEGIN ATOMIC
SIGNAL SQLSTATE '74444' ('OSG HAS A FRIEND');
END#


The goal of the trigger is to warn us when an OSG has a friend (basically, if a similar record exists within the context of the same owner) at time of insert.
The trigger works as desired (returning a 74444 and the message when a Friend exists... and not otherwise) with one exception-- by using "signal sqlstate" it returns a negative sqlCode (-438) which rolls back the transaction.

I'm looking for a way to ultimately prevent the rollback of the transaction.
Can I set the sqlCode or somehow tell db2 not to rollback the transaction?

I know I could execute the select after the insert from our java app instead of via the trigger, but that is less desirable in today's environment. I also know I could use a unique index, but that would also return a negative sqlCode and rollback the transaction... so we wouldn't be any better off.

I simply want a warning instead of the error/rollback.

Thanks,
Mike

---------------------------------------------------------------------------------
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

Colleen Clow

Re: trigger for info instead of error
(in response to Michael Shields)
Could you signal a different sqlstate? The sqlstate itself is comprised
of a class and a sub-class. The class is the first two characters and it
indicates whether overall execution was successful or not. If you modified
the sqlstate so that the first character is 0 and the second is greater
than 0, that should mean successful execution with a warning. DB2
shouldn't rollback then.

cmc




"Shields Michael"
<[login to unmask email] To: [login to unmask email]
NDEERE.COM> cc:
Sent by: "DB2 Data Subject: trigger for info instead of error
Base Discussion
List"
<[login to unmask email]
>


12/07/2004 12:20 PM
Please respond to
"DB2 Database
Discussion list at
IDUG"





I have created the following trigger:

CREATE TRIGGER test.OSG_ISRT
AFTER INSERT ON test.OSG
REFERENCING NEW AS NEW_OSG
FOR EACH ROW MODE DB2SQL
WHEN (1 <
(
SELECT COUNT(*) FROM ADRDNST.ORD_SHP_GRP
WHERE col1 = NEW_OSG.col1
AND col2 = NEW_OSG.col2
AND col3 = NEW_OSG.col3
AND col4 = NEW_OSG.col4
)
)
BEGIN ATOMIC
SIGNAL SQLSTATE '74444' ('OSG HAS A FRIEND');
END#


The goal of the trigger is to warn us when an OSG has a friend (basically,
if a similar record exists within the context of the same owner) at time of
insert.
The trigger works as desired (returning a 74444 and the message when a
Friend exists... and not otherwise) with one exception-- by using "signal
sqlstate" it returns a negative sqlCode (-438) which rolls back the
transaction.

I'm looking for a way to ultimately prevent the rollback of the
transaction.
Can I set the sqlCode or somehow tell db2 not to rollback the transaction?

I know I could execute the select after the insert from our java app
instead of via the trigger, but that is less desirable in today's
environment. I also know I could use a unique index, but that would also
return a negative sqlCode and rollback the transaction... so we wouldn't be
any better off.

I simply want a warning instead of the error/rollback.

Thanks,
Mike

---------------------------------------------------------------------------------

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




**********
The information contained in this communication is confidential, private, proprietary, or otherwise privileged and is intended only for the use of the addressee. Unauthorized use, disclosure, distribution or copying is strictly prohibited and may be unlawful. If you have received this communication in error, please notify the sender immediately at (312)653-6000 in Illinois; (972)766-6900 in Texas; or (800)835-8699 in New Mexico.
**********

---------------------------------------------------------------------------------
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

David Churn

Re: trigger for info instead of error
(in response to Colleen Clow)
Mike,

Unfortunately, you cannot define an SQLSTATE that is not an error.
When I tried to define a trigger with a 'warning' SQLSTATE the create
trigger blew up with SQLSTATE 428B3 which has the following
explanation. Please note the second point.

-435 AN INVALID SQLSTATE sqlstate IS SPECIFIED IN THE FUNCTION
RAISE_ERROR OR IN A SIGNAL SQLSTATE STATEMENT

Explanation: The SQLSTATE specified in the RAISE_ERROR function or
specified in a SIGNAL SQLSTATE statement of a trigger definition does not
conform to the rules for an application defined SQLSTATE.

System Action: The statement cannot be processed.

Programmer Response: Correct the SQLSTATE specified in the RAISE_ERROR
function or SIGNAL statement. The SQLSTATE must be a character string
containing exactly 5 characters. It must be of type CHAR defined with a
length of 5, or a type VARCHAR defined with a length of 5 or greater. The
SQLSTATE value must follow the rules for application-defined SQLSTATEs as
follows:

o Each character must be from the set of digits ('0' through '9') or
non-accented upper case letters ('A' through 'Z').

o The SQLSTATE class (first two characters) cannot be '00', '01' or '02'
because these are not error classes.

o If the SQLSTATE class (first two characters) starts with the character
'0' through '6' or 'A' through 'H', then the subclass (last three
characters) must start with a letter in the range 'I' through 'Z'.

o If the SQLSTATE class (first two characters) starts with the character
'7', '8', '9' or 'I' though 'Z', then the subclass (last three
characters) can be any of '0' through '9' or 'A' through 'Z'.

SQLSTATE: 428B3


On Tue, 7 Dec 2004 12:20:23 -0600, Shields Michael
<[login to unmask email]> wrote:
> I have created the following trigger:
>
> CREATE TRIGGER test.OSG_ISRT
> AFTER INSERT ON test.OSG
> REFERENCING NEW AS NEW_OSG
> FOR EACH ROW MODE DB2SQL
> WHEN (1 <
> (
> SELECT COUNT(*) FROM ADRDNST.ORD_SHP_GRP
> WHERE col1 = NEW_OSG.col1
> AND col2 = NEW_OSG.col2
> AND col3 = NEW_OSG.col3
> AND col4 = NEW_OSG.col4
> )
> )
> BEGIN ATOMIC
> SIGNAL SQLSTATE '74444' ('OSG HAS A FRIEND');
> END#
>
> The goal of the trigger is to warn us when an OSG has a friend (basically, if a similar record exists within the context of the same owner) at time of insert.
> The trigger works as desired (returning a 74444 and the message when a Friend exists... and not otherwise) with one exception-- by using "signal sqlstate" it returns a negative sqlCode (-438) which rolls back the transaction.
>
> I'm looking for a way to ultimately prevent the rollback of the transaction.
> Can I set the sqlCode or somehow tell db2 not to rollback the transaction?
>
> I know I could execute the select after the insert from our java app instead of via the trigger, but that is less desirable in today's environment. I also know I could use a unique index, but that would also return a negative sqlCode and rollback the transaction... so we wouldn't be any better off.
>
> I simply want a warning instead of the error/rollback.
>
> Thanks,
> Mike
>
> ---------------------------------------------------------------------------------
> 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

Suresh Sane

Re: trigger for info instead of error
(in response to David Churn)
Michael,

I know what you are asking for is definitely possible if the trigger calls
an SP but may also be possible without calling an SP. See table 10-1 from
our redbook SG24-7083.

You need to set the sqlstate to 01Hxy (e.g. 01H12) - note it must start with
01H. Only in this case DB2 returns an sqlcode of +462 (warning) rather than
-438 (error).

Contact me offline if any issues. I think it may work in the trigger too (I
hope!).

Thanks,
Suresh

>From: Shields Michael <[login to unmask email]>
>Reply-To: DB2 Database Discussion list at IDUG <[login to unmask email]>
>To: [login to unmask email]
>Subject: trigger for info instead of error
>Date: Tue, 7 Dec 2004 12:20:23 -0600
>
>I have created the following trigger:
>
>CREATE TRIGGER test.OSG_ISRT
>AFTER INSERT ON test.OSG
>REFERENCING NEW AS NEW_OSG
>FOR EACH ROW MODE DB2SQL
>WHEN (1 <
>(
>SELECT COUNT(*) FROM ADRDNST.ORD_SHP_GRP
>WHERE col1 = NEW_OSG.col1
>AND col2 = NEW_OSG.col2
>AND col3 = NEW_OSG.col3
>AND col4 = NEW_OSG.col4
>)
>)
>BEGIN ATOMIC
>SIGNAL SQLSTATE '74444' ('OSG HAS A FRIEND');
>END#
>
>
>The goal of the trigger is to warn us when an OSG has a friend (basically,
>if a similar record exists within the context of the same owner) at time of
>insert.
>The trigger works as desired (returning a 74444 and the message when a
>Friend exists... and not otherwise) with one exception-- by using "signal
>sqlstate" it returns a negative sqlCode (-438) which rolls back the
>transaction.
>
>I'm looking for a way to ultimately prevent the rollback of the
>transaction.
>Can I set the sqlCode or somehow tell db2 not to rollback the transaction?
>
>I know I could execute the select after the insert from our java app
>instead of via the trigger, but that is less desirable in today's
>environment. I also know I could use a unique index, but that would also
>return a negative sqlCode and rollback the transaction... so we wouldn't be
>any better off.
>
>I simply want a warning instead of the error/rollback.
>
>Thanks,
>Mike
>
>---------------------------------------------------------------------------------
>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

David Churn

Re: trigger for info instead of error
(in response to Suresh Sane)
Michael & Suresh,

I tested this using the trigger from before and unfortunately it does
not work. DB2 refuses to bind the trigger and continues to give the
428B3 SQLSTATE on the create trigger.

Dave


On Thu, 9 Dec 2004 22:38:35 -0600, Suresh Sane <[login to unmask email]> wrote:
> Michael,
>
> I know what you are asking for is definitely possible if the trigger calls
> an SP but may also be possible without calling an SP. See table 10-1 from
> our redbook SG24-7083.
>
> You need to set the sqlstate to 01Hxy (e.g. 01H12) - note it must start with
> 01H. Only in this case DB2 returns an sqlcode of +462 (warning) rather than
> -438 (error).
>
> Contact me offline if any issues. I think it may work in the trigger too (I
> hope!).
>
> Thanks,
> Suresh
>
> >From: Shields Michael <[login to unmask email]>
> >Reply-To: DB2 Database Discussion list at IDUG <[login to unmask email]>
> >To: [login to unmask email]
> >Subject: trigger for info instead of error
> >Date: Tue, 7 Dec 2004 12:20:23 -0600
>
>
> >
> >I have created the following trigger:
> >
> >CREATE TRIGGER test.OSG_ISRT
> >AFTER INSERT ON test.OSG
> >REFERENCING NEW AS NEW_OSG
> >FOR EACH ROW MODE DB2SQL
> >WHEN (1 <
> >(
> >SELECT COUNT(*) FROM ADRDNST.ORD_SHP_GRP
> >WHERE col1 = NEW_OSG.col1
> >AND col2 = NEW_OSG.col2
> >AND col3 = NEW_OSG.col3
> >AND col4 = NEW_OSG.col4
> >)
> >)
> >BEGIN ATOMIC
> >SIGNAL SQLSTATE '74444' ('OSG HAS A FRIEND');
> >END#
> >
> >
> >The goal of the trigger is to warn us when an OSG has a friend (basically,
> >if a similar record exists within the context of the same owner) at time of
> >insert.
> >The trigger works as desired (returning a 74444 and the message when a
> >Friend exists... and not otherwise) with one exception-- by using "signal
> >sqlstate" it returns a negative sqlCode (-438) which rolls back the
> >transaction.
> >
> >I'm looking for a way to ultimately prevent the rollback of the
> >transaction.
> >Can I set the sqlCode or somehow tell db2 not to rollback the transaction?
> >
> >I know I could execute the select after the insert from our java app
> >instead of via the trigger, but that is less desirable in today's
> >environment. I also know I could use a unique index, but that would also
> >return a negative sqlCode and rollback the transaction... so we wouldn't be
> >any better off.
> >
> >I simply want a warning instead of the error/rollback.
> >
> >Thanks,
> >Mike
> >
> >---------------------------------------------------------------------------------
> >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
>

---------------------------------------------------------------------------------
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