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.

Good answers provided, identified biggest issues (mutating table exception ("each row" trigger not allowed to select from the table), probably want AFTER INSERT OR UPDATE trigger. There's also some corner cases to consider handling, such as update to orig_tab.pk (probably do not want to leave "old" row in reporting table); or what if row already exists in reporting table when matching row is inserted to orig_tab. — spencer7593, Jun 25 '12 at 16:05

Sathyajith Bhat · Answer 1 · 2012-06-21T10:07:08.770

3

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;

edited Jun 21 '12 at 10:07

answered Jun 21 '12 at 10:02

Sathyajith Bhat

21,321
22
95
134

Thanks Sathya for your response. A little problem I can see with updating: If a record is updated in orig_tab and no corresponding record in rep_tab then this logic might not work – Jaanna Jun 21 '12 at 10:39
@Jaanna you can check for the presence of the record & insert it, if it's not present – Sathyajith Bhat Jun 21 '12 at 10:44
elsif updating then if rep_tab.pk <> :old.pk then INSERT INTO rep_tab (pk,name) VALUES (:NEW.pk, :NEW.name); else UPDATE rep_tab r SET name = :NEW.name WHERE r.pk = :old.pk; endif; doesn't work – Jaanna Jun 21 '12 at 11:30
@Jaanna er, you can't refer to a record table just by using `tablename.columnname` – Sathyajith Bhat Jun 21 '12 at 11:33

spencer7593 · Accepted Answer · 2012-06-22T23:06:53.113

There are some corner cases that aren't handled in previous answers.

What if a matching pk already exists in the reporting table, when a row is inserted. (We wouldn't normally expect this to happen, but consider what would happen if someone deleted a row from the orig_tab, and then inserted it again. (This is the kind of problem that's going to crop up in production, not in test, at the most inopportune time. Better to plan for it now.)

BEGIN
   IF inserting THEN
      -- insure we avoid duplicate key exception with a NOT EXISTS predicate
      INSERT INTO rep_tab(pk,name)
      SELECT :new.pk, :new.name FROM DUAL
      WHERE NOT EXISTS (SELECT 1 FROM rep_tab WHERE pk = :new.pk);
      -- if row already existed, there's a possibility that name does not match
      UPDATE rep_tab t SET t.name = :new.name 
       WHERE t.pk = :new.pk;
      -- could improve efficiency of update by checking if update is actually
      -- needed using a nullsafe comparison ( t.name <=> :new.name );
   ELSIF updating THEN
      -- handle updates to pk value (note: the row to be updated may not exist
      -- so we need to fallthru to the merge)
      IF :new.pk <> :old.pk THEN
         UPDATE rep_tab t
            SET t.pk = :new.pk
              , t.name = :new.name
          WHERE t.pk = :old.pk ;
      END IF;
      MERGE INTO rep_tab d
      USING DUAL ON (d.pk = :old.pk)
      WHEN MATCHED THEN
      UPDATE SET d.name = :new.name
      WHEN NOT MATCHED THEN
      INSERT (d.pk,d.name) VALUES (:new.pk,:new.name);
   END IF;
END;

Gaurav Soni · Answer 3 · 2012-06-21T18:26:17.130

1

This is an Extension of Sathya Answer as Jaanna asked about if the record is updating in orrig_tab and no corresponding record in rep_tab then the below logic will cater the request below .Please don't judge me with this answer as this solution belongs to Sathya

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
       MERGE INTO rep_tab d
          USING DUAL
       ON (d.pk =:OLD.pk)
       WHEN MATCHED THEN
         UPDATE SET d.name = :OLD.name            
       WHEN NOT MATCHED THEN
         INSERT (d.pk,d.name) VALUES (:OLD.PK,:NEW.PK );
    END IF;
END vr_reporting_trigger;

edited Jun 21 '12 at 18:26

answered Jun 21 '12 at 18:15

Gaurav Soni

6,278
9
52
72

@Guarav: on the INSERT (when not matched), shouldn't that be `VALUES (:new.PK, :new.name)` ? And on the UPDATE (when matched) should't that be `d.name = :new.name ` ? Does this handle the case when the value of the pk column is updated? – spencer7593 Jun 22 '12 at 22:30
@GauravSoni your comment should have been on my answer, I didn't get the notification. Also, don't add semicolons to the name. Having said that, this seems fine. – Sathyajith Bhat Jun 25 '12 at 15:39

PLSQL Trigger to update field value in another table

3 Answers3