In this post i will share some scenarios that you might face with ADF Master Detail Table component. Typically those mentioned below:-

  1. Create a row in child table on creation of row in master table (uses Accessor to programmatically create a row in child )
  2. Using Cascade delete option on committed rows

The code sample is based upon scott schema and uses dept and emp tables. The dept table serves as master table and the emp table serves as child table. Also,the association relationship between the entities involved is a composition relationship.

There are a few pre-requisites for this sample to work, basically, you need to create triggers on both the tables and database sequences that will be used to populate  primary key values. The SQL script is present in the sample app.

 

1. Creating row in child table whenever a row is created in master table:-

To accomplish this i have created a method in the application module implementation class that creates a row for department programmatically and then uses the exposed accessor to create the row in child. The snippet is shown below :- 

    /**
     * Creates and inserts Dept and Emp Row
     */
    public void createDeptAndEmpRow(){
        DeptVOImpl deptVO=this.getDeptVO1();
        DeptVORowImpl row=(DeptVORowImpl)deptVO.createRow();
        deptVO.insertRow(row);
        RowIterator iterator= row.getEmpVO();
        Number deptNumber=row.getDeptno().getSequenceNumber();
        NameValuePairs nvps=new NameValuePairs();
        nvps.setAttribute("Deptno", deptNumber);
        EmpVORowImpl empRow=(EmpVORowImpl)iterator.createAndInitRow(nvps);
        iterator.insertRow(empRow);
        }


Here i have used the *VOImpl and *VORowImpl class implementations. Also note  the partial triggers on employee table for “create department and employee button” and “Delete department” button which causes the employee table to refresh.



2. Using cascade delete option on committed rows:-



Default database foreign key constraints ensure that you cannot delete rows from a parent while records exist in child table, so if you issue a delete statement on master table i.e Department table you will receive a  foreign key constraint violation exception. The solution is to make the foreign key constraint deferrable which ensures that validation happens when you issue a  commit and not while you issue the delete statement. So to get this to work drop the existing constraint and recreate it as following. 



ALTER TABLE emp
ADD CONSTRAINT fk_deptno
FOREIGN KEY (deptno)
REFERENCES dept (deptno)
ON DELETE CASCADE
DEFERRABLE
INITIALLY DEFERRED ;


 



Also note the “ON DELETE CASCADE” clause which will delete the records in child table whenever a row from master table is deleted.



 



The database scripts for triggers and sequences are present in the sample project which can be downloaded from here.



masterdetailexample



 associationrelationship