Home » Fusion Middleware & Colab Suite » Weblogic & Application Server » Oracle Trigger
Oracle Trigger [message #76942] Wed, 31 March 2004 17:46 Go to next message
phelix
Messages: 3
Registered: March 2004
Junior Member
Hi, all

2 questions about oracle trigger

1. how do I catch an error in the trigger? Let's say I do an insert into another table in a trigger, If it inserts a duplicate key into the table. I can not catch this error in my java program, how could I do that. Can the trigger return something says there is an error?

2. Does update statement can return the number of row effected?

I need to update a record if it is exist else insert if it is not in the table. If I can know the number of row effected. Then I do not need to do a select first, if the row exist in the table then update otherwise insert. I can just do update then if number of row effected =0 do insert.

Thanks a lot

 

 
Re: Oracle Trigger [message #76945 is a reply to message #76942] Thu, 01 April 2004 08:11 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
You shouldn't need a trigger, but error handling in a trigger works just like in any other pl/sql. If the error isn't handled completely then it'll propogate out to the client.
-- if the update is expected to succeed most of the time
-- try it first and then insert if that fails
update t set col2 = 123 where col1 = v1;
if sql%rowcount = 0 then
   insert into t values (v1, 123);
end if;

-- if the insert is expected to succeed most of the time
-- try it first and then update if that fails
   insert into t values (v1, 123);
exception
   when dup_val_on_index then
      update t set col2 = 123 where col1 = v1;
Previous Topic: How to upgrade Oracle [8.1.7] IAS's Apache?
Next Topic: Using 9iAS, mod_osso with Oracle 8i database ?
Goto Forum:
  


Current Time: Thu Mar 28 16:27:45 CDT 2024