Oracle Trigger to insert /update to another table

Question

Basically, i wanted to create a oracle trigger, which will track the Insert/Updates in a table and i wanted to insert these changed records alone into a new table on a daily basis.This new table data will be using for my daily data refresh (more like a CDC approach)

I was using below code. My expectation was when my CUSTOMER table got INSERT/UPDATED,that record should be available in CUSTOMER_D table.But i am missing something in below code

CREATE OR REPLACE TRIGGER CUST_TRIG AFTER INSERT OR UPDATE ON CUSTOMERS
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW WHEN (OLD.ID <> NEW.ID)
begin
  IF INSERTING THEN
    begin
    INSERT INTO CUSTOMERS_D
      (ID, NAME, AGE, ADDRESS, SALARY) values
      (:new.ID, :new.NAME, :new.AGE, :new.ADDRESS, :new.SALARY);
    -- assuming, there is an unique key on id
    exception
      when dup_val_on_index then
        null;
    end;

  ELSIF UPDATING THEN
    IF :NEW.ID = :OLD.ID THEN
      UPDATE CUSTOMERS_D DWT
         SET DWT.ID = :NEW.ID,
             DWT.NAME = :NEW.NAME,
             DWT.AGE = :NEW.AGE,
             DWT.ADDRESS = :NEW.ADDRESS,
             DWT.SALARY = :NEW.SALARY;
    END IF;
    MERGE INTO CUSTOMERS_D D
    USING DUAL
    ON (D.ID = :NEW.ID)
    WHEN MATCHED THEN
      UPDATE SET D.NAME = :NEW.NAME,
             D.AGE = :NEW.AGE,
             D.ADDRESS = :NEW.ADDRESS,
             D.SALARY = :NEW.SALARY
    WHEN NOT MATCHED THEN
      INSERT
        (D.ID, D.NAME, D.AGE, D.ADDRESS, D.SALARY) VALUES
        (:NEW.ID, :NEW.NAME, :NEW.AGE, :NEW.ADDRESS, :NEW.SALARY);
  END IF;

end test;

Well, every body is new when they learn a language for the first time. Come up with a code, there are always examples out there which are away from a single google-search. Ask a question only when you cannot find a solution anywhere.Give me a code questions are not always welcome here in SO — Kaushik Nayak
Sure Kaushik.Thanks. I am editing my above post by adding the code which i tried — vak
Well, I don't see a point in your Trigger code. If ultimately what you're going to do is to Merge on cutomers_d using cutomers, they both are going to contain the same data, except the ones that were deleted. So, you will basically be wasting resources by having 2 tables with almost the same data. — Kaushik Nayak
That means you would want only an insert into CUSTOMERS_D when there's a change in cutomers ( i.e an update or insert ), tell me If I'm wrong? — Kaushik Nayak
Ok. My actual plan is, whenever the source data changes, trigger has to notify and those changed records to be get inserted into 1 table .Then this data set need to be converted as a JSON file, which i will publish as Google Cloud event using PubSub.Then load this data to Bigquery. This is my overall flow. May be my explanation above is not proper. As part of testing purpose i kept CUST as source and CUST_D as a table which need to be converted as JSON file. Kindly advise — vak

Kaushik Nayak Kaushik Nayak · Accepted Answer · 2019-04-02T07:07:06

Your trigger in its present form has too many unnecessary constructs which you should get rid of. All you need ( as you confirmed in the comments ) is a single insert statement into customers_d whenever a record gets added or inserted from customers table.
I would recommend you to add another column indicating the time of transaction -MODIFIED_TIME

CREATE OR REPLACE TRIGGER CUST_TRIG 
  AFTER INSERT OR UPDATE ON CUSTOMERS 
FOR EACH ROW 
begin
   INSERT INTO CUSTOMERS_D
     (ID, NAME, AGE, ADDRESS, SALARY,modified_time ) 
       values (:new.ID,
       :new.NAME, 
       :new.AGE, 
       :new.ADDRESS, 
       :new.SALARY,
       systimestamp);
end ;
/

DEMO

Oracle Trigger to insert /update to another table

2 Answers