How can I move existing records from parent table to newly created child table with the help of triggers (Oracle)?

Question

I want to create a trigger wherein the child table pulls all the exisiting records from parent table.

I have created child tables referencing the parent table primary key as foreign key in new table

CREATE OR REPLACE TRIGGER trigger name
AFTER INSERT ON table_name(parent_table)
FOR EACH ROW 
BEGIN 
   insert statement for child table;
END;

Above trigger is created for records which will be inserting post to child table creation, I would like to push all old records (which were present prior to child table creation) to the new child table. Will triggers help in pulling all the old records ?

Answer 1

Obviously NO!!

The trigger will start inserting records to the child table from the parent table post creation of trigger. Records inserted into parent table prior to trigger creation need to be inserted manually.

If you want to pull all records before the creation of trigger into child table from parent table then you need to write one-time INSERT query as following:

INSERT INTO CHILD_TABLE
SELECT * FROM PARENT_TABLE P 
WHERE NOT EXISTS (SELECT 1 FROM CHILD_TABLE C WHERE P.PK = C.FK);

-- OR --

INSERT INTO CHILD_TABLE
SELECT * FROM PARENT_TABLE P 
WHERE P.PK IN 
(SELECT PK FROM PARENT_TABLE
MINUS
SELECT FK FROM CHILD_TABLE
)

Cheers!!