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));
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
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.
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.