I have two tables Table 1 and Table 2, both tables have unique id's. Table 1 has Table 2 id as a foreign key.
Table 1
id table2 id
---------------
1 NULL
2 NULL
Table 2
id date
---------------
1 '28/12/2019'
2 '30/12/2019'
What SQL query to run, every time a new date is added to table 2 date, sync with table 1 data?
Maybe the following example will help (Oracle 18c). Ideas: {1} Synchronize the tables with MERGE. {2} Use a procedure for INSERTing into both tables.
Tables for testing
-- parent (always use DATE for dates!)
create table table2 ( id primary key, date_ )
as
select 1, to_date( '28/12/2019', 'DD/MM/YYYY' ) from dual union all
select 2, to_date( '30/12/2019', 'DD/MM/YYYY' ) from dual ;
-- child
create table table1 (
id number generated always as identity start with 5000 primary key
, table2id number references table2( id ) not null unique
) ;
-- the 2 tables contain the following data:
SQL> select * from table2 ;
ID DATE_
---------- ---------
1 28-DEC-19
2 30-DEC-19
SQL> select * from table1 ;
no rows selected
{1} MERGE
-- initial sync (P)arent <-> (C)hild
merge into table1 C
using table2 P on ( C.table2id = P.id )
when not matched then
insert ( C.table2id ) values ( P.id ) ;
-- 2 rows merged.
-- data
SQL> select * from table2 ;
ID DATE_
---------- ---------
1 28-DEC-19
2 30-DEC-19
SQL> select * from table1 ;
ID TABLE2ID
---------- ----------
5000 1
5001 2
{2} PROCEDURE
-- assumption: ("parent" table) table2 id generated by a sequence
-- sequence: use nextval and currval.
create sequence t2seq start with 2000 increment by 1 ;
create or replace procedure insert2 ( dt_ date )
is
begin
insert into table2 ( id, date_ ) values ( t2seq.nextval, dt_ ) ;
insert into table1 ( table2id ) values ( t2seq.currval ) ;
end ;
/
{3} Testing
begin
for i in 100 .. 105
loop
insert2( sysdate + i ) ; -- call the procedure, insert some dates
end loop;
commit ;
end ;
-- check:
SQL> select * from table1 ;
ID TABLE2ID
---------- ----------
5000 1
5001 2
5002 2000
5003 2001
5004 2002
5005 2003
5006 2004
5007 2005
8 rows selected.
SQL> select * from table2 ;
ID DATE_
---------- ---------
1 28-DEC-19
2 30-DEC-19
2000 07-APR-20
2001 08-APR-20
2002 09-APR-20
2003 10-APR-20
2004 11-APR-20
2005 12-APR-20
8 rows selected.
{4} Try to sync again -> 0 rows merged.
merge into table1 C using table2 P on ( C.table2id = P.id )
when not matched then
insert ( C.table2id ) values ( P.id ) ;
0 rows merged.
DBfiddle here. NEXTVAL and CURRVAL documentation here.
If you simply want a record to be inserted into table 1 when a record in table 2 is created, I would recommend adding after insert trigger on table 2. Here's a link that shows code for such a trigger. They use the example of an order table with an order audit table to keep track of order changes.
https://www.techonthenet.com/oracle/triggers/after_insert.php
Example trigger from the link above for posterity.
CREATE OR REPLACE TRIGGER orders_after_insert
AFTER INSERT
ON orders
FOR EACH ROW
BEGIN
-- Insert record into audit table
INSERT INTO orders_audit
( order_id,
quantity,
cost_per_item,
total_cost,
username )
VALUES
( :new.order_id,
:new.quantity,
:new.cost_per_item,
:new.total_cost,
user ); -- Find username of person performing the INSERT into the table
END;
I'm assuming you have OneToOne relation between these two tables and you are keeping your audit recored separated from main records. If this is the case:
So one way is to add trigger into the table2.
keep in mind that on the same session you get the same CURRVAL of the sequence. and this two tables are sharing the sequence value
CREATE OR REPLACE TRIGGER TRIGGER1
BEFORE INSERT ON TABLE_2
FOR EACH ROW
WHEN (new.ID IS NULL)
BEGIN
:new.ID := TABLE_SEQ.NEXTVAL;
INSERT INTO TABLE_1(ID,TABLE2_ID) VALUES(TABLE_SEQ.CURRVAL,TABLE_SEQ.CURRVAL);
END;
you also need to have a sequence.
CREATE SEQUENCE TABLE_SEQ
START WITH 1
INCREMENT BY 1;
