Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to Maintain History table using Oracle SQL Developer

User_0WCIAJul 5 2021 — edited Jul 5 2021

I need to maintain history of changes into target table. If there is any update, delete or insert into source table it should be reflected into target table and latest record should be marked as Y for each ID. Attached image for better understanding.
oracle.JPG (70.2 KB)--CREATE SOURCE AND TARGET TABLES
CREATE TABLE SOURCE (ID NUMBER, NAME VARCHAR(20));
CREATE TABLE TARGET(ID NUMBER, NAME VARCHAR(20), ACTIVE_FLAG VARCHAR2(2));
STEP 1 (LOADING SORCE TABLE)
INSERT INTO SOURCE VALUES (1,'AAA');
INSERT INTO SOURCE VALUES (2,'BBB');
SOURCE TABLE
image.pngEXPECTED TRAGET TABLE
image.png
STEP 2 (INSET AND UPDATE INTO SOURCE TABLE)
INSERT INTO SOURCE VALUES (3,'CCC');
UPDATE TABLE SOURCE
SET NAME = 'ZZZ'
WHERE ID =1;
SOURCE TABLE
image.pngEXPECTED TARGET TABLE
image.png
It would be great help if someone could help me to generate the code for this.

This post has been answered by Solomon Yakobson on Jul 5 2021
Jump to Answer
Comments
Post Details
Added on Jul 5 2021
25 comments
6,488 views