Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE x (
  code INT PRIMARY KEY,
  col1 INT,
  col2 INT,
  dt   DATE
);
CREATE TABLE y (
  code INT,
  col1 INT,
  col2 INT,
  dt   DATE,
  PRIMARY KEY ( code, dt )
);
CREATE TYPE y_object IS OBJECT (
  code INT,
  col1 INT,
  col2 INT,
  dt   DATE
);
CREATE TYPE y_table IS TABLE OF y_object;
CREATE TRIGGER log_y_changes_to_x
FOR INSERT OR UPDATE ON y
COMPOUND TRIGGER
  y_data y_table := y_table();
AFTER EACH ROW
  IS
  BEGIN
    y_data.EXTEND(1);
    y_data(y_data.COUNT) := y_object( :NEW.code, :NEW.col1, :NEW.col2, :NEW.dt );
  END AFTER EACH ROW;
AFTER STATEMENT
  IS
  BEGIN
    MERGE INTO x
    USING (
      SELECT code,
             col1,
             col2,
             dt
      FROM   (
        SELECT t.*,
               ROW_NUMBER() OVER ( PARTITION BY code ORDER BY dt DESC ) rn
        FROM   TABLE(y_data) t
      )
      WHERE rn = 1
    ) y
    ON ( y.code = x.code )
    WHEN MATCHED THEN
      UPDATE
      SET   col1 = y.col1,
            col2 = y.col2,
            dt   = y.dt
      WHERE y.dt >= x.dt
    WHEN NOT MATCHED THEN
      INSERT ( code, col1, col2, dt )
      VALUES ( y.code, y.col1, y.col2, y.dt );
  END AFTER STATEMENT;
END;
/
INSERT INTO y ( code, col1, col2, dt )
SELECT 1, 0, 2, DATE '2020-01-01' FROM DUAL UNION ALL
SELECT 2, 1, 0, DATE '2020-01-01' FROM DUAL UNION ALL
SELECT 2, 0, 3, DATE '2020-01-02' FROM DUAL UNION ALL
SELECT 3, 3, 3, DATE '2020-01-01' FROM DUAL;
4 rows affected

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
SELECT * FROM x;


CODE
COL1
COL2
DT


2
0
3
2020-01-02 00:00:00

3
3
3
2020-01-01 00:00:00

1
0
2
2020-01-01 00:00:00


UPDATE y
SET    col1 = col1 + 3
WHERE  code <= 2
AND    dt = DATE '2020-01-01';
2 rows affected

SELECT * FROM x;


CODE
COL1
COL2
DT


2
0
3
2020-01-02 00:00:00

3
3
3
2020-01-01 00:00:00

1
3
2
2020-01-01 00:00:00


INSERT INTO y ( code, col1, col2, dt )
SELECT 3, 5, 5, DATE '2020-01-05' FROM DUAL;
1 rows affected

SELECT * FROM x;


CODE
COL1
COL2
DT


2
0
3
2020-01-02 00:00:00

3
5
5
2020-01-05 00:00:00

1
3
2
2020-01-01 00:00:00


INSERT INTO y ( code, col1, col2, dt )
SELECT 3, 4, 4, DATE '2020-01-04' FROM DUAL;
1 rows affected

SELECT * FROM x;


CODE
COL1
COL2
DT


2
0
3
2020-01-02 00:00:00

3
5
5
2020-01-05 00:00:00

1
3
2
2020-01-01 00:00:00

CODE	COL1	COL2	DT
2	0	3	2020-01-02 00:00:00
3	3	3	2020-01-01 00:00:00
1	0	2	2020-01-01 00:00:00

CODE	COL1	COL2	DT
2	0	3	2020-01-02 00:00:00
3	5	5	2020-01-05 00:00:00
1	3	2	2020-01-01 00:00:00