This discussion is archived
1 Reply Latest reply: Jan 24, 2013 2:34 PM by CraigB RSS

delete record

982158 Newbie
Currently Being Moderated
Hi,
I have 2 tables (EMP and DEPT) ,the table EMP has a foreign key(ID_DEPT) ..
I have DEPT interface to view and delete one element of the table DEPT, when I click on the delete button the system displays a message error:
ora-02292 integrity constraint Violated
I know it is normal that the system displays this message because there is an integrity constraint between the two tables.
but I want to display my own message when the user tries to delete a record!
Here is the code:

declare
     i number;
begin

     i:= show_alert('supp');
     if i=alert_button1 then
          delete_record;
          commit;
          message('success');
     end if;
     
     exception when others then
message('This id is used in the EMP table');
message('This id is used in the EMP table');
end;

*=> I want the message on the exception will be displayed*
  • 1. Re: delete record
    CraigB Guru
    Currently Being Moderated
    Delete your "EXCEPTION WHEN OTHERS" from your When-Button-Pressed trigger and handle the error in the On-Error trigger. For example:
    /* Sample Module/Form level On-Error trigger */
    DECLARE
       err_num NUMBER := ERROR_CODE;
       err_type VARCHAR2(3) := ERROR_TYPE;
       err_msg VARCHAR2(80) := ERROR_TEXT;
       db_err_num  NUMBER := DBMS_ERROR_CODE;
       db_err_msg  VARCHAR2(200) := DBMS_ERROR_TEXT;
    BEGIN
       IF ( err_num = 40735 ) THEN 
       --Unhandled Exception
          IF ( db_err_num = 2292 ) THEN 
             -- Integrity Constraint Violation...
             Message('This id is used in the EMP table');
             Message('This id is used in the EMP table');
          ELSIF ( db_err_num IS NOT NULL ) THEN 
             Message(db_err_msg);
             Message(db_err_msg);
          END IF;
       ELSE
          Message(err_msg);
          Message(err_msg);
       END IF;
    END;
    Hope this helps.
    Craig...

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points