473,322 Members | 1,755 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Exception handling within triggers

Hi,

I am new to the db2 world.
How do you handle exceptions within triggers.
I was trying to port following trigger from Oracle:

CREATE TRIGGER DB2ADMIN.TEST_ARI
AFTER INSERT ON DB2ADMIN.TEST
REFERENCING NEW AS NEW
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC

insert into SYSTEM_LOG (error_id, error_message) values ('1', 'Hello
there. Record id#: ' || NEW.id);

EXCEPTION WHEN OTHER THEN
insert into SYSTEM_LOG (error_id, error_message) values ('1', 'ERROR
FOR id#: ' || NEW.id);

END;
END:

It complains about the EXCEPTION WHEN OTHER line.
I was wandering if this is allowed in DB2 at all. If not what is the
alternative to capturing the exception within a trigger.

I have replication going on between SQL2000 and DB2. Once a record is
replicated to DB2, trigger is fired to insert/update some other
tables. If there is an exception in the trigger it kills entire
replication. I want to capture it and log it and then continue
replicating.
Please help.

Thanks,
Tzar
Nov 12 '05 #1
4 7417
DB2 for Multiplatfoms currently does not support exception handling in
triggers.
If any exception arises in the trigger the statement calling the trigger
will be rolled back.
In the next point release you will be able to place this complex logic into
a procedure and CALL the proc
from the trigger.

I'm abit curious what you expect thsi exception to be though. Why is this
handler needed?

Cheers
Serge
Nov 12 '05 #2
I have replication going on between SQL2000 and DB2. Once a record is
written into DB2 (through replication) a trigger is fired to further
distribute data within DB2.

If one of the triggers fail, it stops entire replication. I need
replication going on even if a trigger fails. I just want to log which
trigger failed.

I might need to look for my answer in the SQL2000 replication because
that is where the transaction originates.
If you have better idea, I am open for suggestions :-)

Cheers,
Tzar

"Serge Rielau" <sr*****@ca.eyebeem.com> wrote in message news:<bn**********@hanover.torolab.ibm.com>...
DB2 for Multiplatfoms currently does not support exception handling in
triggers.
If any exception arises in the trigger the statement calling the trigger
will be rolled back.
In the next point release you will be able to place this complex logic into
a procedure and CALL the proc
from the trigger.

I'm abit curious what you expect thsi exception to be though. Why is this
handler needed?

Cheers
Serge

Nov 12 '05 #3
AK
I was wondering if SQL2000 replication is capable of running stored
procedures rather than plain inserts
Nov 12 '05 #4
Tzar <dv*******@olgc.ca> wrote:
I have replication going on between SQL2000 and DB2. Once a record is
written into DB2 (through replication) a trigger is fired to further
distribute data within DB2.

If one of the triggers fail, it stops entire replication. I need
replication going on even if a trigger fails. I just want to log which
trigger failed.


Do you know what exactly could cause a failure or could it be just about
anything? If you know what could happen, you could test for those
conditions in the trigger before the operation of the trigger body is
actually done.

Btw, are you using db2 replication? If you do, you could possibly define
several members in the subscription set and let the replication component
do the "further data distribution". Then you don't have to worry about any
errors because replication itself will handle them directly. (If you use
other replication tools, you will most probably have the same or similar
functionality, but I'm just not very familiar with those tools.)

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: Noor | last post by:
please tell the technique of centralize exception handling without try catch blocks in c#.
5
by: A | last post by:
Hi, I'm having some difficulty understanding the semantics of the rethrow keyword. Consider the following code: int main(){ try{ ... } catch(SomeException &SE){
4
by: maricel | last post by:
I have the following base table structure - DDL: CREATE TABLE "ADMINISTRATOR"."T1" ( "C1" INTEGER NOT NULL ) IN "TEST_TS" ; ALTER TABLE "ADMINISTRATOR"."T1" ADD PRIMARY KEY
44
by: craig | last post by:
I am wondering if there are some best practices for determining a strategy for using try/catch blocks within an application. My current thoughts are: 1. The code the initiates any high-level...
1
by: Noor | last post by:
Hi all, I am trying to catch all types of exceptions from a app regardless of whether it is in debugger mode( VS development environment) or run the.exe file outside the IDE. My App...
2
by: Carol | last post by:
Exception may be thrown in the code inside the try block. I want to handling the SqlException with State == 1 in a special way, and for all others I want to use a general way to handle. Which of...
3
by: Allie | last post by:
Hi, all. I've never been any good at handling exceptions... So I need your help. Within a try{ } block, I have to call a function that has to throw its own exception. How should I go about...
3
by: George2 | last post by:
Hello everyone, Suppose I have some objects created on local function stack (not on heap). And I allocate and free all the related resources in the constructor and destructor (e.g. memory and...
1
by: George2 | last post by:
Hello everyone, Such code segment is used to check whether function call or exception- handling mechanism runs out of memory first (written by Bjarne), void perverted() { try{
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.