PLSQL Trigger to update field value in another table

Question

I am quite new to triggers so obviously I am doing something wrong somewhere. I am working on a report table which will get the data from original tables. For the sake of simplicity, let's say that there is one table and then there is one reporting table.

Original table (orig_tab)

CREATE TABLE orig_tab (
PK     NUMBER(8)       not null,
NAME   VARCHAR2(20)            ,
);

INSERT INTO orig_tab (PK, NAME) VALUES (1, 'AAA');
INSERT INTO orig_tab (PK, NAME) VALUES (2, 'BBB');
INSERT INTO orig_tab (PK, NAME) VALUES (3, 'CCC');

Then there is reporting table (rep_tab)

CREATE TABLE rep_tab (
PK     NUMBER(8)       not null,
NAME   VARCHAR2(20)            ,
);

Now from user inteface, someone changes the value of record 2. Obviously, this should be treated as an insert (because this record doesn't exist) for reporting table. Then after sometime, the value is changed so it is an update case for reporting table.

Question: How may I make this kind of trigger? I assume that it is a merge statemement case.

This is what I have done:

create or replace trigger vr_reporting_trigger
after update on orig_tab
  for each row
begin
  MERGE INTO rep_tab d
  USING (SELECT pk FROM orig_tab) s
  ON (d.pk = s.pk)
  WHEN MATCHED THEN
  UPDATE SET d.pk = s.pk,
             d.name = s.name
  WHEN NOT MATCHED THEN
  INSERT (d.pk, d.name) VALUES (s.pk, s.name);
end vr_reporting_trigger;

Any suggestions or recommendations that can help me to figure it out? Thanks.

Answer 1

Merge statement sounds like a plan, except that the trigger won't fire when you're doing the first insert because you've mentioned it's an AFTER UPDATE trigger, not an AFTER INSERT trigger.

Also, the SELECT pk FROM orig_tab will result in Mutating table problem.

Better way would be to define an AFTER INSERT OR UPDATE trigger, combine it with INSERT/UPDATING keywords to handle inserts/updates & use :new/:old to handle new data & old data respectively.

CREATE OR replace TRIGGER vr_reporting_trigger
  AFTER INSERT OR UPDATE ON orig_tab
  FOR EACH ROW
BEGIN
    IF inserting THEN
      INSERT INTO rep_tab
                  (pk,
                   name)
      VALUES      (:NEW.pk,
                   :NEW.name);
    ELSIF updating THEN
      UPDATE rep_tab r
      SET    name = :NEW.name
      WHERE  r.pk = :old.pk;
    END IF;
END vr_reporting_trigger;