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
– Kaushik Nayak, Commented Apr 2, 2019 at 4:58
Sure Kaushik.Thanks. I am editing my above post by adding the code which i tried — vak
– vak, Commented Apr 2, 2019 at 5:42
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
– Kaushik Nayak, Commented Apr 2, 2019 at 6:27
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
– Kaushik Nayak, Commented Apr 2, 2019 at 6:45
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
– vak, Commented Apr 2, 2019 at 6:48

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

2

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

answered Apr 2, 2019 at 7:07

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

valex · Accepted Answer · 2019-04-02 07:08:05Z

1

At first glance I can see the one issue in this trigger when new record is inserted the OLD.ID will be NULL so WHEN ((OLD.ID <> NEW.ID) will be FALSE and the trigger wont be invoked on INSERT into CUST_TRIG. Just add following condition:

FOR EACH ROW  WHEN ((OLD.ID <> NEW.ID) OR (OLD.ID IS NULL))

answered Apr 2, 2019 at 7:08

valex

24.2k7 gold badges47 silver badges62 bronze badges

Collectives™ on Stack Overflow

Oracle Trigger to insert /update to another table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related