Home » Developer & Programmer » Application Express & MOD_PLSQL » Validating the master data isn't in use before delete (APEX 3.2)
Validating the master data isn't in use before delete [message #540887] Thu, 26 January 2012 08:46 Go to next message
balckbandit5
Messages: 104
Registered: December 2011
Senior Member
Hello Smile

I'm having trouble getting this to work; what I have is a main table LEGAL_PERSON, a master data table ROLE, and a sort of link table LEGAL_PERSON_ROLE which just has 2 columns LEGAL_PERSON_ID and ROLE_CODE.
The ROLE table is fully editable in a master data table. So what I need is a validation to make sure no one is 'using' that role before I delete it
i.e. that the ROLE_CODE selected doesn't appear in the LEGAL_PERSON_ROLE table.
I've made an SQL 'Exisits' Validation with the code:
select LPR.ROLE_CODE 
from LEGAL_PERSON_ROLE LPR, ROLE R 
where LPR.ROLE_CODE = R.ROLE_CODE

but obviously that doesn't work properly because I need to identify the code(s) which are selected with a checkbox. Unfortunately I do not know how to do that...
The tabular form I made just from the wizard with all the default settings.

Thank you in advance for all/any help Smile
Re: Validating the master data isn't in use before delete [message #541010 is a reply to message #540887] Fri, 27 January 2012 01:41 Go to previous messageGo to next message
Littlefoot
Messages: 19540
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Such a "validation" should be kept within the database, by the means of foreign key constraints (referential integrity). Database would make sure that you can't delete master record as long as its child record exists.

Here's an example:
SQL> create table t_role (id number primary key, r_name varchar2(20));

Table created.

SQL> create table t_legal_person_role (id number primary key,
  2                                    id_role number constraint fk_role references t_role (id));

Table created.

SQL> insert all
  2    into t_role (id, r_name) values (1, 'Role 1')
  3    into t_role (id, r_name) values (2, 'Role 2')
  4    into t_legal_person_role (id, id_role) values (100, 2)
  5  select * from dual;

3 rows created.

SQL> commit;

Commit complete.

SQL> delete from t_role where id = 1;

1 row deleted.

Now, the interesting part: you can't delete a role that is protected by referential integrity constraint:
SQL> delete from t_role where id = 2;
delete from t_role where id = 2
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.FK_ROLE) violated - child record found


SQL>


Database would just propagate error to front end (Apex in your case).

[Updated on: Fri, 27 January 2012 01:42]

Report message to a moderator

Re: Validating the master data isn't in use before delete [message #541021 is a reply to message #541010] Fri, 27 January 2012 02:09 Go to previous messageGo to next message
balckbandit5
Messages: 104
Registered: December 2011
Senior Member
ok I see, is there a way to edit the error from the database? So if the user tries to delete something they get an error they understand?
Re: Validating the master data isn't in use before delete [message #541034 is a reply to message #541021] Fri, 27 January 2012 03:26 Go to previous messageGo to next message
balckbandit5
Messages: 104
Registered: December 2011
Senior Member
HI, I found this on another page which does exactly what I want:
DECLARE
   nb number;
BEGIN
    FOR ii IN 1 .. ApexLib_TabForm.getRowCount
    LOOP
       IF ApexLib_TabForm.isRowSelectorChecked(ii)
       THEN 
           select count(*) into nb from LEGAL_PERSON_EXPERTISE_AREA 
           where EXPERTISE_AREA_ID = ApexLib_TabForm.NV('EXPERTISE_AREA_ID', ii);
           IF nb > 0
           THEN
               ApexLib_Error.addError
                 ( pError      => 'Expertise Area cannot be deleted'
                 , pColumnName => 'EXPERTISE_AREA_NAME'
                 , pRow        => ii
                 );

           END IF;
       END IF;
    END LOOP;
    --
    RETURN ApexLib_Error.getErrorStack;
END;
So I tried to copy it to the relevant page and change the tables etc to the relevant ones:
DECLARE
   nb number;
BEGIN
    FOR ii IN 1 .. ApexLib_TabForm.getRowCount
    LOOP
       IF ApexLib_TabForm.isRowSelectorChecked(ii)
       THEN 
           select count(*) into nb from LEGAL_PERSON_ROLE 
           where LEGAL_ROLE_CODE = ApexLib_TabForm.NV('LEGAL_ROLE_CODE', ii);
           IF nb > 0
           THEN
               ApexLib_Error.addError
                 ( pError      => 'Legal Role cannot be deleted'
                 , pColumnName => 'LEGAL_ROLE_NAME'
                 , pRow        => ii
                 );

           END IF;
       END IF;
    END LOOP;
    --
    RETURN ApexLib_Error.getErrorStack;
END;

But this doesn't work...when it runs it displays an error:
ORA-01722: invalid number
ERR-1024 Unable to run "function body returning text" validation.
But I don't know why, it works on the first one, but not on the second...

[Updated on: Fri, 27 January 2012 04:09]

Report message to a moderator

Re: Validating the master data isn't in use before delete [message #541090 is a reply to message #541034] Fri, 27 January 2012 09:26 Go to previous message
balckbandit5
Messages: 104
Registered: December 2011
Senior Member
I fixed it, the problem was that LEGAL_ROLE_CODE isn't a number, though it has a unique constraint it isn't the primary key. So I changed the database round a bit and changed all the 'code's to 'id' and its all done. Just in case anyone has a similar problem in future.
Previous Topic: Implementing page level authentication
Next Topic: Right To Left Application
Goto Forum:
  


Current Time: Tue Sep 02 23:36:22 CDT 2014

Total time taken to generate the page: 0.11821 seconds