PLSQL Trigger to update field value in another table

Question

I'm new to triggers, so obviously I'm doing something wrong. I am working on a report table that will receive data from source tables. For simplicity, we say that there is one table, and then there is one report 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 the report table appears (rep_tab)

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

Now someone from the user inteface is changing the value of record 2. Obviously, this should be considered as an insert (since this record does not exist) for the report table. Then, after some time, the value changes, so it is an update case for the report table.

Question: How can I make this trigger? I suppose this is a merger case.

This is what I did:

 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 figure this out? Thanks.

Answer 1

There are some angular cases that are not handled in previous answers.

What to do if pk mapping already exists in the report table when a row is inserted. (Usually we did not expect this to happen, but think about what happens if someone removes the line from orig_tab and then inserts it again. (This is the problem that occurs during production, and not in the test, to the most inappropriate time. Better schedule 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 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; 

Answer 2

The merge operator is similar to a plan, except that the trigger does not work when you do the first insertion because you mentioned the AFTER UPDATE trigger, not the AFTER INSERT trigger.

In addition, SELECT pk FROM orig_tab will lead to a mutation table problem .

It is best to define an AFTER INSERT or UPDATE trigger, combine it with the INSERT / UPDATING keywords to handle inserts / updates, and use :new / :old to process new data and 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; 

Answer 3

This is a Sathya Answer extension like Jaanna , asked by the question whether the record in orrig_tab is updated and there is no corresponding record in rep_tab, then the logic below will satisfy the request below. Please do not judge me with this answer, as this decision belongs to Satya

 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;