Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    25

    Question Unanswered: [Oracle 9i] Table Audit Trigger

    I need to create a trigger that allows to store the following information on every UPDATE, INSERT or DELETE:

    the table name,
    the field name,
    current user name (DB user),
    the old value of each changed column,
    the new value of each changed column,
    the current date

    The log table should look like this:

    | table | field | user | old | new | date |

    so i need to fetch the .old and .new values in one trigger.

    I don't want to use audit!

    Any ideas ?

    Thx

    Don.Zwiebel

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: [Oracle 9i] Table Audit Trigger

    You will need a trigger on each table that looks something like this:
    PHP Code:
    create or replace trigger emp_audit_trg
    after insert 
    or update or delete on emp
    for each row
    begin
      audit_pkg
    .audit'EMP''EMPNO', :old.empno, :new.empno );
      
    audit_pkg.audit'EMP''ENAME', :old.ename, :new.ename );
      
    audit_pkg.audit'EMP''DEPTNO', :old.deptno, :new.deptno );
      
    audit_pkg.audit'EMP''SAL', :old.sal, :new.sal );
      
    audit_pkg.audit'EMP''HIREDATE', :old.hiredate, :new.hiredate );
    end
    The audit procedure is overloaded for each datatype like this:
    PHP Code:
    create or replace package audit_pkg as
      
    procedure auditp_tablename varchar2p_columnname varchar2,
        
    p_oldval varchar2p_newval varchar2 );
      
    procedure auditp_tablename varchar2p_columnname varchar2,
        
    p_oldval numberp_newval number );
      
    procedure auditp_tablename varchar2p_columnname varchar2,
        
    p_oldval datep_newval date );
    end
    Each of the overloaded procedures will have code like this:
    PHP Code:
      procedure auditp_tablename varchar2p_columnname varchar2,
        
    p_oldval varchar2p_newval varchar2 )
      
    is
      begin
        
    if (p_oldval is null and p_newval is not null)
        or (
    p_oldval is not null and p_newval is null)
        or (
    p_oldval != p_newval)
        
    then
          insert into audit
    tablenamecolumnnameusernameoldvalnewvalauditdate )
          
    values (p_tablenamep_columnnameUSERp_oldvalp_newvalSYSDATE );
        
    end if;
      
    end
    (The number and date versions would use TO_CHAR - be sure to preserve time information in dates).

    As you can probably guess, I've done this before! The first objection I expect is: "But do I really have to specify all the column names in the trigger? Can't I use ALL_TAB_COLUMNS and dynamic SQL to avoid that?" - to which the answer is "no, you can't!": there is simply no way to access the :old and :new values in dynamic SQL. What you can easily do however is write a program to generate the trigger code in that way, i.e. a SQL script or stored procedure that you call with a table name and it outputs the trigger code as shown above.

  3. #3
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    25

    No field names

    But what if I don't know the field names?

    I've got to fetch the field names from a table (e.g. FLDS) (no system tables!)

    So I've got a variable field name to combine with ld. and :new.

    Are there any possibilities ?

    Thx again

    Don.Zwiebel
    Last edited by don.zwiebel; 04-14-04 at 09:14.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: No field names

    How could you, as the writer of the trigger code, not know the names of the columns in the table?

    If you have a table called FLDS that contains the auditable column names for a table, then you could use that instead of ALL_TAB_COLUMNS to generate the trigger code as I said earlier.

    There are no possibilities for combining a variable name and the :old and :new prefixes directly within the trigger code. None whatsoever. This is hard to believe at first sight ("surely I can construct some dynamic SQL like 'insert into x values (:new.' || v_columname || ')' , can't I?" But the fact is you cannot!

  5. #5
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    25
    So there are no possibilities to get column names from a table an fetch the old and new values ?

    What about auditing ?

    How does it work ?

    Greetz

    Don.Zwiebel

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I can't tell you much about Oracle's auditing - look at the Application Developer's Guide for some details about "Fine Grained Auditing"; it may do what you want.

    I'm not quite clear about what you are trying to do exactly. Why is there a table of column names involved? Is it to specify precisely which columns of each table are audited? If so, you could do it like this...

    Suppose the table looks like this:

    create table audit_columns
    ( table_name varchar2(30)
    , column_name varchar2(30)
    );

    (If a column is in the table, we must audit it; if not, not.)

    Now all we need to do is modify the audit procedure(s) from earlier:
    PHP Code:
    procedure auditp_tablename varchar2p_columnname varchar2,
      
    p_oldval varchar2p_newval varchar2 )
    is
    begin
      
    if audit_requiredp_tablenamep_columnname ) -- New function
      and ( (
    p_oldval is null and p_newval is not null)
      or (
    p_oldval is not null and p_newval is null)
      or (
    p_oldval != p_newval) )
      
    then
        insert into audit
    tablenamecolumnnameusernameoldvalnewvalauditdate )
        
    values (p_tablenamep_columnnameUSERp_oldvalp_newvalSYSDATE );
      
    end if;
    end
    The audit_required function returns TRUE or FALSE depending whether tha table/column specified are in the table.

    And for completeness, here is the code to generate an appropriate trigger for any given table:
    PHP Code:
    procedure gen_audit_trig
    p_tablename in varchar2
    )
    is
      procedure p
    t varchar2 is begin dbms_output.put_line(t); end;
    begin
      p 
    ('create or replace trigger ' || p_tablename || '_AUDIT_TRG' );
      
    ('after insert or update or delete on ' || p_tablename );
      
    ('for each row' );
      
    ('begin');
      for 
    r in (select column_name from user_tab_columns where table_name p_tablename)
      
    loop
        p 
    ('  audit_pkg.audit(''' || p_tablename || ''', ''' || r.column_name
          
    || ''', :old.' || r.column_name || ', :new.' || r.column_name || ');' );
      
    end loop;
      
    ('end;');
    end
    It works like this:
    PHP Code:
    SQLset serverout on size 1000000 format wrapped
    SQL
    exec gen_audit_trig('EMP')
    create or replace trigger EMP_AUDIT_TRG
    after insert 
    or update or delete on EMP
    for each row
    begin
      audit_pkg
    .audit('EMP''EMPNO', :old.EMPNO, :new.EMPNO);
      
    audit_pkg.audit('EMP''ENAME', :old.ENAME, :new.ENAME);
      
    audit_pkg.audit('EMP''JOB', :old.JOB, :new.JOB);
      
    audit_pkg.audit('EMP''MGR', :old.MGR, :new.MGR);
      
    audit_pkg.audit('EMP''HIREDATE', :old.HIREDATE, :new.HIREDATE);
      
    audit_pkg.audit('EMP''SAL', :old.SAL, :new.SAL);
      
    audit_pkg.audit('EMP''COMM', :old.COMM, :new.COMM);
      
    audit_pkg.audit('EMP''DEPTNO', :old.DEPTNO, :new.DEPTNO);
    end;

    PL/SQL procedure successfully completed
    Just spool that to a file and you are done!

  7. #7
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    25
    The following problem:

    We've got a table TBLS and a table FLDS where the tables and columns are stored (and some options for our programs!)

    What I need now is a way to audit a table and its columns (fetched from the above tables!) and store the information in another table which looks like this:

    | table | field | user | old | new | date |

    On SQLServer I solved the problem by using inserted and deleted.

    The only thing I had to know was the name of the table. The fields, are fetched from another table.

    Now I need to find a way doing so on Oracle 9. And for compatibility the upper table structure is to be kept!

    Any Ideas ?

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Which part of this requirement haven't I already covered? As I have already said, there is no alternative to hard-coding the names of the columns in the triggers, so the fact that they are listed in a table is immaterial, unless you want to use that table to specify a subset of the columns to be audited - either at runtime (in the audit_pkg code) or when generating the trigger code (in the gen_audit_trig procedure).

    In any case, something seems wrong about your audit table. Suppose the user USER1 does this:

    update emp
    set sal=5000
    where empno=1234;

    Your table would show:

    EMP | SAL | USER1 | 4000 | 5000 | 14-APR-2004

    You know that USER1 changed a row in table EMP - but which one?

  9. #9
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    25
    And what about PL/SQL ? Is there a possibillity to execute Strings?

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by don.zwiebel
    And what about PL/SQL ? Is there a possibillity to execute Strings?
    Yes, there is:
    PHP Code:
    SQL> declare
      
    2    v_sql varchar2(2000) := 'begin dbms_output.put_line(''hello''); end;';
      
    3  begin
      4    execute immediate v_sql
    ;
      
    5  end;
      
    6  /
    hello

    PL
    /SQL procedure successfully completed
    But that won't help you to write a trigger that audits :old and :new column values without having to hard-code the column names into the trigger, as I have already said above. The sad fact is that the keywords OLD and NEW are meaningless outside the context of the trigger, and dynamic SQL and PL/SQL are executed outside the context of the trigger.

  11. #11
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    25
    okay thx.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •