Auditing DML changes in Oracle

Question

I want to log all tables changes in one table named Tables_History with one trigger for each table, and whithout addressing column names explicity, and according to the type of column. Because i have 50 tables that belonges to different projects. Structure of tables may be change by many developers. So i can't address column names explicity. This is defenition of Tables_History:

create table Tables_History
(
  user_id        VARCHAR2(10) not null,
  change_date    DATE not null,
  table_name     VARCHAR2(100) not null,
  column_name    VARCHAR2(100) not null,
  primary_key_id INTEGER not null,
  old_number     NUMBER,
  new_number     NUMBER,
  old_string     VARCHAR2(900),
  new_string     VARCHAR2(900),
  old_date       DATE,
  new_date       DATE,
  ...
  .
);

If there is any way to access :new column values in a trigger, by passing column name as a parameter instead of standard way (:new.column_name). something like this (:new['column_name']).
This is my ideal trigger for one table (then i can copy+paste this trigger for other tables and change just few parameter :-D ):

create or replace trigger Audit_TableName
  before update on TableName
  for each row
declare
  v_query  varchar(32767);
  UserCode varchar(10);
begin

  SELECT sys_context('USERENV', 'CLIENT_IDENTIFIER')
  INTO UserCode
  FROM DUAL;             

  FOR getrec IN (SELECT column_name, data_type
                   FROM all_tab_columns
                  WHERE table_name = 'TableName'
                    AND owner = 'MEHRAN'
                    AND data_type <> 'BLOB') LOOP
    if Updating(getrec.column_name) then
      if getrec.data_type = 'NUMBER' then
        v_query := 'insert into Tables_History(user_id,change_date,table_name,column_name,primary_key_id,,,OLD_NUMBER, NEW_NUMBER)'
           ||'values('..,..,..,..||:old[getrec.column_name]||','||:new[getrec.column_name]||')';
      else if getrec.data_type = 'VARCHAR2' then
        v_query := 'insert into Tables_History(...,...,... OLD_VARCHAR, NEW_VARCHAR)'
           ||'values(...,...,..'||:old[getrec.column_name]||','||:new[getrec.column_name]||')';
      ...
      .
      .  
      end if;
      EXECUTE IMMEDIATE v_query;
    end if;
  END LOOP;

end Audit_TableName;

Try to print the dynamic query you build; this will give you some hints to edit your code — Aleksej, Sep 19 '16 at 07:28
I don't think that is possible using a trigger. Even if you make it dynamic, i doubt it will work. btw why do you want to do this way. — XING, Sep 19 '16 at 07:39
Provide some more detail in this regard. Tell us how do you want your trigger to look alike? Share the sample code or output which you need. — Prashant Mishra, Sep 19 '16 at 07:43

score 2 · Answer 1 · answered Sep 19 '16 at 07:56

2

You can't do it this way. You can write a stored procedure to generate a trigger that statically references the new/old values: https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:59412348055

answered Sep 19 '16 at 07:56

Evgeniy Kiskin

199
4

score 0 · Answer 2 · answered Sep 24 '16 at 09:35

0

I think (don't have first hand experience) that Flashback Data Archive can do what you want, if you're using Oracle11 or greater.

answered Sep 24 '16 at 09:35

archimede

706
5
9

Auditing DML changes in Oracle

2 Answers2