Triggers calling SPs - question

Paul Walters

Triggers calling SPs - question
We are trying to enforce a standard that triggers don't do any
updates/deletes/inserts - they call a stored procedure to do the required task.

The problem that we are seeing is the if the SP receives a negative sql code how
do we pass that information back to the triggering event.

Is anyone calling sps from a trigger and how are you passing failure events back
to the triggering event?

We are running DB2 V6.

Thanks



HEPP SHERY C

Re: Triggers calling SPs - question
(in response to Paul Walters)
Paul- we have the same standard set up here- and this is an interesting
question that you bring up. For now- I have to warn you it's not pretty- we
have the stored procedure issue a rollback. From my recollection of testing
this causes an error in the trigger- but I think it's a generic one. It
doesn't actually capture what the real error is. In v7 you will be able to
issue rollbacks in stored procedures so I'm sure we'll have to revisit this
at that time.

Hth's

Shery

-----Original Message-----
From: Paul A Walters [mailto:[login to unmask email]
Sent: Tuesday, December 18, 2001 6:53 AM
To: [login to unmask email]
Subject: Triggers calling SPs - question

We are trying to enforce a standard that triggers don't do any
updates/deletes/inserts - they call a stored procedure to do the required
task.

The problem that we are seeing is the if the SP receives a negative sql code
how
do we pass that information back to the triggering event.

Is anyone calling sps from a trigger and how are you passing failure events
back
to the triggering event?

We are running DB2 V6.

Thanks








Ben Reches

Re: Triggers calling SPs - question
(in response to HEPP SHERY C)
Paul,

We found that aborts are percolated up to the client program causing the trigger to be invoked. So, we created a 'C' sproc which issues an abort() and the client program sees -723. It can
then decide whether to continue processing.
I believe there is an apar out there that specifies that issuing a ROLLBACK or COMMIT in a sproc will generate a negative sql code at the client program. But this is a temporary solution,
since ROLLBACK and COMMIT are due to become valid SPL statements.

Ben

Paul A Walters wrote:

> We are trying to enforce a standard that triggers don't do any
> updates/deletes/inserts - they call a stored procedure to do the required task.
>
> The problem that we are seeing is the if the SP receives a negative sql code how
> do we pass that information back to the triggering event.
>
> Is anyone calling sps from a trigger and how are you passing failure events back
> to the triggering event?
>
> We are running DB2 V6.
>
> Thanks
>
>
>