Oracle Trigger: conditional insert or update

Question

I want to create a trigger which it is responsible for inserting/updating data into table x when data is inserted to table y.

table y has the field code and if a record with the same code already exists in the table x then the trigger should update the record else it should insert a new records into table x.

I am new to oracle and know how to create a trigger for insertion or update. But, I do not know how create one to both insert and update based on the aforementioned condition.

Any help is appreciated.

EDIT

To shed more light on the issue:

Table y can have multiple records with the same code while table x can only have one record for each code(to keep only the latest status).

So, When a new record is inserted to table y I should decide whether I am going to insert the records on the table x or I am just gonna update the row which contains the code.

Answer 1

You can use a compound trigger to collate all the inserts and updates and then issue a single MERGE statement (rather than doing one MERGE for each changed row).

For example, if you have table Y that can contain data for multiple dates:

CREATE TABLE y (
  code INT,
  col1 INT,
  col2 INT,
  dt   DATE,
  PRIMARY KEY ( code, dt )
);

And table X that should contain the data for the latest date:

CREATE TABLE x (
  code INT PRIMARY KEY,
  col1 INT,
  col2 INT,
  dt   DATE
);

And want to keep X updated (but don't want to create it as a VIEW or MATERIALIZED VIEW and aren't going to UPDATE the DT column in table Y) then you could use the compound trigger:

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;
/

And have the types:

CREATE TYPE y_object IS OBJECT (
  code INT,
  col1 INT,
  col2 INT,
  dt   DATE
);

CREATE TYPE y_table IS TABLE OF y_object;

And you insert data:

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;

Then table X contains:

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

And, if you update data:

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

Then table X contains *(note: the code 2 row was not updated as it was not the latest row):

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

And:

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

Then X contains

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

And:

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

Then X is unchanged as the row is older than the previous.

db<>fiddle here