1 Reply Latest reply: Jan 24, 2013 4:34 PM by CraigB RSS

    delete record

    Goldray
      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
          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...