title: PLSQL Trigger to update field value in another table
body: <p>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.</p>
  
  <p>Original table (orig_tab)</p>
  
  <pre><code>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');
  </code></pre>
  
  <p>Then there is reporting table (rep_tab)</p>
  
  <pre><code>CREATE TABLE rep_tab (
  PK     NUMBER(8)       not null,
  NAME   VARCHAR2(20)            ,
  );
  </code></pre>
  
  <p>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.</p>
  
  <p>Question: How may I make this kind of trigger? I assume that it is a merge statemement case.</p>
  
  <p>This is what I have done:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>Any suggestions or recommendations that can help me to figure it out? Thanks.</p>