title: Merging in trigger with select insert
body: <p>I have posted this question before. But now my project manager came back and gave me a new set of instructions. Again, I am a bit lost now and tried to fix it to some extend.</p>
  
  <p>I am working on a trigger which needs INSERT INTO with WHERE logic.</p>
  
  <p>I have three tables.</p>
  
  <p>Absence_table:</p>
  
  <pre><code>-----------------------------
  |  user_id | absence_reason |
  -----------------------------
  |  1234567 |   40           |
  |  1234567 |   50           |
  |  1213    |   40           |
  |  1314    |   20           |
  |  1111    |   20           |
  -----------------------------
  </code></pre>
  
  <p>company_table:</p>
  
  <pre><code>-----------------------------
  | user_id  | company_id     |
  -----------------------------
  | 1234567  |  10201         |
  | 1213     |  10200         |
  | 1314     |  10202         |
  | 1111     |  10200         |
  -----------------------------
  </code></pre>
  
  <p>employment_table:</p>
  
  <pre><code>-------------------------------------------
  | user_id  |   emp_type    |  employee_id |
  -------------------------------------------
  | 1234567  |   Int         |    1         |
  | 1213     |   Int         |    2         |   
  | 1314     |   Int         |    3         |
  | 1111     |   Ext         |    4         |
  -------------------------------------------
  </code></pre>
  
  <p>and finally I have the table out where data should be going only who have emp_type = Int in employment_table and have company_id = 10200</p>
  
  <p>out:</p>
  
  <pre><code>-------------------------------------------
  | employee_id | absence_reason |  user_id |
  -------------------------------------------
  |  1          |    40          |  1234567 |
  |  1          |    50          |  1234567 |
  |  2          |    40          |  1213    |
  |  3          |    20          |  1314    |
  -------------------------------------------
  </code></pre>
  
  <p>Here is my trigger:</p>
  
  <pre><code>CREATE OR REPLACE TRIGGER "INOUT"."ABSENCE_TRIGGER" 
     AFTER INSERT ON arc_hrcs.absences_data 
     FOR EACH ROW
  DECLARE
  BEGIN
    CASE
        WHEN UPDATING THEN
             MERGE INTO out o USING DUAL ON (out.user_id =:NEW.user_id)
             WHEN MATCHED THEN UPDATE SET
                        out.employee_id = (SELECT employee_id FROM employment_table WHERE user_id = :NEW.user_id),   
                        out.absence_reason = :NEW.absence_reason,
                        out.user_id = :NEW.user_id
             WHEN NOT MATCHED THEN 
  
  
       insert into out (absence_reason, employee_id)
  select :NEW.absence_reason, e.employee_id
    from employment_table e 
         inner join company_table c
                 on c.user_id = e.user_id
   where e.user_id = :NEW.user_id
     and e.emp_type = 'INT'
     and c.company_id = '10200';
    END CASE;
  END absence_trigger;
  </code></pre>
  
  <p>I can't figure out how to change the code after WHEN NOT MATCHED THEN code according to the merging syntax. A bit of guide would help me out :-)</p>
  
  <p>Thanks in advance.</p>