| Oracle Trigger [message #76942] |
Wed, 31 March 2004 17:46  |
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  |
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;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
