sahar:

My tables have these structures (in oracle):

---table grandmother ---
  ID
  some other columns


---table mother---
  ID
  grandmother_ID
  some other columns

---table girl
  ID
  mother_ID
  some other columns

---table grandchild1
  ID
  girl_ID
  some other columns 

---table grandchild2
  ID
  girl_ID
  some other columns 

Now, how can i write a procedure to Delete one record from grandmother table ? It is clear that we should delete records with grandmother_IDs from mother table and mother_IDs from girl table and girl_IDs from grandchild1 and grandchild2.

create or replace PROCEDURE GRAND_MOTHER_DELETE
( 
    IN_ID      IN      GRAND_MOTHER.ID%TYPE 
 )
 AS 
 BEGIN

  DELETE FROM GRAND_CHILD1 GC1 , GRAND_CHILD2 GC2 USING GC1 , GC2
  WHERE GC1.GIRL_ID = GC2.GIRL_ID
  AND GC2.GIRL_ID IN(   
  SELECT ID FROM GIRL WHERE MOTHER_ID IN (
  SELECT ID FROM MOTHER WHERE GRAND_MOTHER_ID=IN_ID));      

  DELETE
  FROM   GRAND_MOTHER
  WHERE  ID  = IN_ID; 

 END;

But it gives syntax error.