Oracle 7.0 database trigger - How to trap error from sql forms

From: Senthil Balasubramaniam <sebalasu_at_uncc.edu>
Date: 12 Dec 1994 23:46:55 GMT
Message-ID: <3cindf$3rp_at_news.uncc.edu>


Keywords:
Hi,

   I have a question about trapping database trigger error from sqlforms.

Here is the database trigger:
create or replace trigger trigger1
AFTER insert or delete or update of field1 on table1 FOR each row
Declare
Begin

IF inserting then
Begin

   insert into table2 values (...);
 exception
  when others then
   raise_application_error(-20004,'* Error inserting .... *'); End;
End if;

End;
/

This trigger should raise an error if a record exists in table2 and you are trying to insert a record in table1 (assume that there is a relationship between these two tables)

I am able to raise the database trigger error from sqlplus. i.e., When i try to insert a record into table1, it tries to insert a record into table2 and the record already exists in table2 and ite raises 'ORA-?????, Unique constraint .....' error. I am not sure about the exact message. (basically I am talking about dup_val_on_index error).

Problem: I am trying to trap the error from sqlforms. When i try to insert a record into table1 from sqlforms it raises - form level 'unable to insert record' error message. And i have to hit display error key to see the error message. As a programmer i don't mind doind this. But when a customer sees this king of error he may not like it.

So, i would like someone to help trapping the database trigger error. Oracle mannual says i have to an insert on on-insert trigger and do exception handling to trap this error. But, for your info. this table (table1) has about 79 fields and is called from 7 forms. I can't go to each form and add insert into .... statements.
(Also, it will be headache to maintain this code).

(P.S: I am new ro oracle 7 version. And i do not have access to most of the oracle
mannuals).

Your help will be appreciated.

Thanks in advance.

Mohan. Received on Tue Dec 13 1994 - 00:46:55 CET

Original text of this message