create table gTree(
	person number(5) primary key, 
	name varchar2(50) not null, 
	father references gTree(person), 
	mother references gTree(person), 
	DescendantsNumber number(3) default 0 not null);  

create or replace procedure increaseAscendent(pperson gTree.person%type) is
	pfather gTree.person%type;
	pmother gTree.person%type;
begin
	if pperson is not null then
		update gTree set DescendantsNumber = DescendantsNumber + 1
		Where person = pperson;
		select father, mother into pfather, pmother
		from gTree where person = pperson;
		increaseAscendent(pfather);
		increaseAscendent(pmother);
	end if;
end;
/

create or replace trigger insGtree
before insert on gTree
for each row
begin
	increaseAscendent(:new.father);
	increaseAscendent(:new.mother);
end;
/

create or replace procedure decreaseAscendent(pperson gTree.person%type) is
	pfather gTree.person%type;
	pmother gTree.person%type;
begin
	if pperson is not null then
		update gTree set DescendantsNumber = DescendantsNumber - 1
		Where person = pperson;
		select father, mother into pfather, pmother
		from gTree where person = pperson;
		decreaseAscendent(pfather);
		decreaseAscendent(pmother);
	end if;
end;
/

create or replace trigger delGtree
before delete on gTree
for each row
begin
	decreaseAscendent(:old.father);
	decreaseAscendent(:old.mother);
end;
/

SCOTT@orcl_11g> CLEAR COLUMNS
SCOTT@orcl_11g> COLUMN name FORMAT A15
SCOTT@orcl_11g> SELECT * FROM gtree
  2  /

    PERSON NAME                FATHER     MOTHER
---------- --------------- ---------- ----------
         1 Herman
         2 violet
         3 Donald
         4 McDonald
         5 Noel                     1          2
         6 Ann                      3          4
         7 Robert                   3          4
         8 Helga
         9 Bill
        10 Barbara                  5          6
        11 Lisa                     9          6
        12 christine                7          8
        13 Inge                     7          8
        14 Erika                    7          8
        15 Michael
        16 Sean                    15         11

16 rows selected.

SCOTT@orcl_11g> CREATE OR REPLACE VIEW gtree_view AS
  2  SELECT c.person as id, c.name AS person, f.name AS father, m.name AS mother,
  3  	    (SELECT COUNT (*)
  4  	     FROM   gtree
  5  	     START  WITH father = c.person OR mother = c.person
  6  	     CONNECT BY PRIOR person = father OR person = mother) AS descendants
  7  FROM   gtree c, gtree f, gtree m
  8  WHERE  c.father = f.person (+)
  9  AND    c.mother = m.person (+)
 10  START  WITH c.father IS NULL
 11  CONNECT BY PRIOR c.person = c.father
 12  /

View created.

SCOTT@orcl_11g> COLUMN person FORMAT A15
SCOTT@orcl_11g> COLUMN father FORMAT A15
SCOTT@orcl_11g> COLUMN mother FORMAT A15
SCOTT@orcl_11g> SELECT * FROM gtree_view
  2  /

        ID PERSON          FATHER          MOTHER          DESCENDANTS
---------- --------------- --------------- --------------- -----------
         1 Herman                                                    2
         5 Noel            Herman          violet                    1
        10 Barbara         Noel            Ann                       0
         2 violet                                                    2
         3 Donald                                                    5
         6 Ann             Donald          McDonald                  2
         7 Robert          Donald          McDonald                  3
        12 christine       Robert          Helga                     0
        13 Inge            Robert          Helga                     0
        14 Erika           Robert          Helga                     0
         4 McDonald                                                  5
         8 Helga                                                     3
         9 Bill                                                      1
        11 Lisa            Bill            Ann                       1
        15 Michael                                                   1
        16 Sean            Michael         Lisa                      0

16 rows selected.

SCOTT@orcl_11g>