title: auditing 50 columns using oracle trigger
body: <p>I need to create a <code>trigger</code> in <code>oracle 11g</code> for auditing a table .</p>
  
  <p>I have a table with <code>50 columns</code> that need to be <code>audited</code>.</p>
  
  <ul>
  <li>For <code>every new insert</code> into a table  ,i need to put an entry in <code>audit table (1 row)</code>.</li>
  <li>For <code>every update</code> ,suppose i update <code>1st 2nd column</code> ,then it will create two record in audit with its  <code>old value  and new value</code> .</li>
  </ul>
  
  <p><em>structure of audit table will be</em> </p>
  
  <pre><code> id        NOT NULL
   attribute NOT NULL
   OLD VALUE NOT NULL
   NEW VALUE NOT NULL
   cre_date  NOT NULL
   upd_date  NULL
   cre_time  NOT NULL
   upd_time  NULL
  </code></pre>
  
  <p>In case of <code>insert</code> ,only the primary key (main table)i.e the <code>id</code>  and <code>cre_date and cre_time</code> need to be populated and <code>attribute</code> equal to <code>*</code> ,in case of update ,suppose colA and colB is updating then all need to be populated.In this case two records will be created with attribute of first record <code>colA</code> and corresponding <code>old and new</code> value , and same for the <code>colB</code></p>
  
  <p>Now my solution to audit is <code>not very optimized</code> , i have created a <code>row level trigger</code> ,which will check for each and every 50 columns for that table whether it is been <code>changed</code> or not based on its <code>new and old value</code>(if -else) , and  it will populate the audit table .
  I am not satisfied with my soltuion thats why i am posting here.
  Another solution which i have seen in the link below :</p>
  
  <p><code>http://stackoverflow.com/questions/1421645/oracle-excluding-updates-of-one-column-for-firing-a-trigger</code> </p>
  
  <p>This is not working in my case , I have done a POC for that  as shown below:</p>
  
  <pre><code>create table temp12(id number);
  
  create or replace trigger my_trigger
  after update or insert on temp12
  for each row
  declare
    TYPE tab_col_nt IS table of varchar2(30);
  
    v_tab_col_nt tab_col_nt;
  
  begin
   v_tab_col_nt := tab_col_nt('id','name');
  
     for r in v_tab_col_nt.first..v_tab_col_nt.last
     loop
        if updating(r) then
           insert into data_table values(1,'i am updating'||r);
        else
        insert into data_table values(2,'i am inserting'||r);
        end if;
     end loop;
  
   end;
  </code></pre>
  
  <p>In case of updating it is calling the else part i don't know why .
     Can this be possible through <code>compound trigger</code></p>