Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2013
    Posts
    46

    Unanswered: Delete from table B when all duplicates deleted from table A

    Hi all,

    I've read on cascade delete and it seems effective when we have pk-fk relationship. I have a requirement as follows:

    Parent.column1 may have duplicate records
    Child.column1 may not have duplicate records

    I have an insert trigger to insert only distinct values into child.column1

    I need a delete logic to delete from child only when all duplicates of a particular value from parent have been deleted.
    I tried a delete trigger (before and after) to count the records where it = old.value but apparently i can not read from same table the delete occurred. before or after trigger here didn't work.

    any suggestions welcome!

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please be more specific/concrete ways.
    Datatype, nullable/notnull of parent.column1 and child.column1?
    Which are primary key? child.column1 may be...

    Please supply DDLs(CREATE TABLE statements, so on...) and INSERT statements to populate the tables.


    By the way,
    you worte
    I have an insert trigger to insert only distinct values into child.column1
    Why did you used more complicated/troublesome way?
    Unique(or Primary key) consraints might be enough to guarantee "to insert only distinct values".

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    does the child have to be deleted right away? this is something you may want to do in a batch process. once a day/week/month "delete from child where not exists(select 1 from parent where child.key = parent.key"
    One other very hokey and complex method I can think of to do it immediate by triggers would be something like:
    add column to child parent_counter
    when row is inserted in child put in count of parent records
    (if a new parent row can be inserted after the child exists, then an insert trigger on parent to update the childs parent_counter(add one) if the child exists
    a delete trigger on the parent to subtract 1 from the child's parent_counter
    an update trigger on child if parent_counter = 0 then delete

  4. #4
    Join Date
    Sep 2013
    Posts
    46
    Quote Originally Posted by tonkuma View Post
    Please be more specific/concrete ways.
    Datatype, nullable/notnull of parent.column1 and child.column1?
    Which are primary key? child.column1 may be...
    No PK-FK is defined. Columns are nullable, but I will change it to not null as there may not be nulls allowed. PFB ddl:

    CREATE TABLE PARENT
    ( "TBL" VARCHAR2(30 CHAR));
    CREATE TABLE CHILD
    ( "TBL" VARCHAR2(30 CHAR));

    Why did you used more complicated/troublesome way?
    Unique(or Primary key) consraints might be enough to guarantee "to insert only distinct values".
    I tried using a unique constraint on child.column1 and it restricted adding duplicate values in parent through the following trigger:

    CREATE OR REPLACE TRIGGER INSERT_TO_CHILD
    AFTER INSERT ON PARENT
    FOR EACH ROW
    BEGIN

    INSERT INTO CHILD(TBL) VALUES(:NEW.TBL);

    END;
    /

    I issued the following insert statements:
    Insert into PARENT (TBL) values ('SOME_TABLE');
    Insert into PARENT (TBL) values ('SOME_TABLE');

    First statement was inserted into parent, while second statement violated unique constraint.

  5. #5
    Join Date
    Sep 2013
    Posts
    46
    Quote Originally Posted by dav1mo View Post
    does the child have to be deleted right away?
    Yes, records must be deleted right away which is why batch process is not preferred method.

  6. #6
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by jigmod01 View Post
    Yes, records must be deleted right away which is why batch process is not preferred method.
    Concider re-designing your model. It is not even possible to declare an referential constraint between parent and child. Mean while here's a hack that might work:

    Code:
    CREATE TABLE PARENT
    ( "TBL" VARCHAR(30) not null);
    CREATE TABLE CHILD
    ( "TBL" VARCHAR(30) not null primary key);
    
    CREATE OR REPLACE TRIGGER INSERT_TO_CHILD
    AFTER INSERT ON PARENT
    REFERENCING NEW AS N
    FOR EACH ROW
    BEGIN
        MERGE INTO CHILD x
        USING (
            VALUES (N.TBL)
        ) y (TBL)
            ON x.TBL = y.TBL
        WHEN NOT MATCHED THEN
            INSERT (TBL) VALUES (y.TBL); --
    END;
    
    CREATE OR REPLACE TRIGGER DELETE_FROM_CHILD
    AFTER DELETE ON PARENT
    REFERENCING OLD AS O
    FOR EACH ROW
    WHEN ( (SELECT COUNT(1) FROM PARENT WHERE TBL = O.TBL) = 0 )
    BEGIN
        DELETE FROM CHILD WHERE TBL = O.TBL; --
    END;
    
    Insert into PARENT (TBL) values ('SOME_TABLE');
    Insert into PARENT (TBL) values ('SOME_TABLE');
    
    select * from child;
    
    delete from (
        select * from parent fetch first 1 rows only
    );
    
    select * from child;
    
    delete from (
        select * from parent fetch first 1 rows only
    );
    
    select * from child;
    Last edited by lelle12; 12-20-13 at 08:47.
    --
    Lennart

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •