Master detail relation:-

Relation between blocks can be established, without having constraints at database level.
Create two tables ( empone, deptone ) in the database.
( Execute the following queries )
SQL>  CREATE TABLE EMPONE
(EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2));

INSERT INTO EMPONE VALUES
(7369, 'SMITH', 'CLERK', 7902,
TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
INSERT INTO EMPONE VALUES
(7499, 'ALLEN', 'SALESMAN', 7698,
TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
INSERT INTO EMPONE VALUES
(7521, 'WARD', 'SALESMAN', 7698,
TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
INSERT INTO EMPONE VALUES
(7566, 'JONES', 'MANAGER', 7839,
TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
INSERT INTO EMPONE VALUES
(7654, 'MARTIN', 'SALESMAN', 7698,
TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMPONE VALUES
(7698, 'BLAKE', 'MANAGER', 7839,
TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
INSERT INTO EMPONE VALUES
(7782, 'CLARK', 'MANAGER', 7839,
TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
INSERT INTO EMPONE VALUES
(7788, 'SCOTT', 'ANALYST', 7566,
TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMPONE VALUES
(7839, 'KING', 'PRESIDENT', NULL,
TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMPONE VALUES
(7844, 'TURNER', 'SALESMAN', 7698,
TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);
INSERT INTO EMPONE VALUES
(7876, 'ADAMS', 'CLERK', 7788,
TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMPONE VALUES
(7900, 'JAMES', 'CLERK', 7698,
TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);
INSERT INTO EMPONE VALUES
(7902, 'FORD', 'ANALYST', 7566,
TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMPONE VALUES
(7934, 'MILLER', 'CLERK', 7782,
TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

 

CREATE TABLE DEPTONE
(DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13) );

INSERT INTO DEPTONE VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPTONE VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPTONE VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPTONE VALUES (40, 'OPERATIONS', 'BOSTON');
COMMIT;

Note
There are no constraints in deptone and empone tables.
Example of master-detail form

Create a block using wizard using parent table  i.e. ( deptnone)

Now,
Go to Tools data block wizard
This time create data block using table empone.
Tools Data block wizard Next Table or view – EMPONE Refresh Move all columns from available columns to data base items Next   Remove auto-join data blocks check box Click on create relationship button   Based on join condition Ok Select DEPTONE OK
Detail Item -- Deptno Maser Item – Deptno Next   Data block Name – EMPONE Next   Create data block and then  call layout wizard Finish
Welcome to Layout wizard Next   Canvas – CANVAS 4  ( Do not select new canvas ) Next Move all columns from available items to displayed items Next Next   Tabular   Next
Frame Title – EMP    Records displayed – 10 Next Finish

 

File Save As DEPT_EMP.fmb
Program Compile Module
Program Run Form.
Click on Execute query

Note
Master block contains deptno – 10 Information.
Child block also displays the details of employees working for deptno 10.
Click on next record
Master block contains deptno – 20 Information.
Child block also displays the details of employees working for deptno 20.
Observe, we have established relation between master block and detail block.
Look at the Object navigator,
You can see both the blocks ( master block and detail block ).
Note
Both the blocks are place in only one canvas i.e. ( CANAVS4 ). Please look at the screen shot.

When we establish Master-detail relationship between two blocks,  the following triggers are created.

• ON-CLEAR-DETAILS
• ON-POPULATE-DETAILS
• ON-CHECK-DELETE-MASTER

1. ON-CLEAR-DETAILS

It is form level trigger. When we clear the master record, the corresponding child records are cleared automatically. Look at the following screen shot.



Keep the cursor on the master block , Record à clear
Deptno 10 record is cleared, and corresponding child records are also deleted automatically.

1. ON-POPULATE-DETAILS

It is a block level trigger.
Retrieves child record automatically, whenever the master( parent ) row is retrieved on to the screen.

ON-CHECK-DELETE-MASTER

It will not allow to remove parent row when depending child row exists.
Keep the cursor in the master block, click on delete button, observe that message in the bottom of the form.  “ Cannot delete master record when matching detail records exist”.