This discussion is archived
11 Replies Latest reply: May 15, 2012 10:50 AM by Justin Cave RSS

DML Trigger for schema

787349 Newbie
Currently Being Moderated
Hi All,

I have a requirement from the client side that .. Need to capture the DML changes for every table in a schema.

DML_AUDIT_LOG tables will have the following structure

Column Name               Description

TABLE_NAME --------------->Table name
COLUMN_NAME--------------->          Column of the table
OLD_VALUE--------------->          Old value of the column if update or delete operation occured
NEW_VALUE--------------->          New value of the column if update or Insert operation occured
USER_ID--------------->               Login User name
AUDIT_LOG_ID--------------->          Primary Key.. we can generate Sequence
TIME_STAMP--------------->          Current timestamp
SOURCE_OF_UPDATE--------------->     From where DML Operation occured i.e SQL Plus, Toad, JDBC Connection
IP_ADDRESS--------------->          IP address of machine where DML Operation occured
AUDIT_ACTION--------------->          DML Operation i.e INSERT, DELETE, INSERT

I have found some solutions from google to archive the above task from


[http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:59412348055]
CREATE OR REPLACE PACKAGE AUDIT_PKG AS
    PROCEDURE CHECK_VAL( TAB_NAME IN VARCHAR2,   COL_NAME IN VARCHAR2,   NEW_VAL IN VARCHAR2,  OLD_VAL IN VARCHAR2 ) ;

END;
/


CREATE OR REPLACE PACKAGE BODY AUDIT_PKG AS
PROCEDURE  CHECK_VAL( TAB_NAME IN VARCHAR2, COL_NAME IN VARCHAR2, NEW_VAL IN VARCHAR2, OLD_VAL IN VARCHAR2 )
IS
BEGIN
    IF ( NEW_VAL <> OLD_VAL OR
         (NEW_VAL IS NULL AND OLD_VAL IS NOT NULL) OR
         (NEW_VAL IS NOT NULL AND OLD_VAL IS NULL) )
    THEN
        INSERT INTO DML_AUDIT_LOG (AUDIT_LOG_SEQ_ID,ENTITY_ID,ENTITY_NAME,COLUMN_NAME,OLD_VALUE,NEW_VALUE,USER_ID,AUDIT_TS,SOURCE_OF_UPDATE,IP_ADDRESS,AUDIT_ACTION)
        VALUES  (1,1, TAB_NAME,COL_NAME,OLD_VAL,NEW_VAL,'USERNAME',SYSTIMESTAMP,'TOAD','10.152.5.64','INSERT');
    END IF;
   
END;

END AUDIT_PKG;
/


SET DEFINE OFF;
CREATE OR REPLACE TRIGGER "ACCOUNT$AUDTRG" 
  AFTER INSERT OR DELETE OR UPDATE ON ACCOUNT FOR EACH ROW
DECLARE 
v_operation VARCHAR2(10) := NULL;
V_TABLE_NAME VARCHAR2(50);
V_IP_ADDRESS VARCHAR2(50);
V_COL_NM VARCHAR2(50);
V_CHECK_VALUE VARCHAR2(150);
BEGIN 
V_TABLE_NAME:='ACCOUNT';
    audit_pkg.check_val('ACCOUNT', 'ACCT_NM', :new.ACCT_NM, :old.ACCT_NM)
END;
/
SHOW ERRORS;
the above trigger works fine if we passes one column.. But my requirement is needs to check for all columns in the table. So I have changed
audit_pkg.check_val('ACCOUNT', 'ACCT_NM', :new.ACCT_NM, :old.ACCT_NM)
as
select '    audit_pkg.check_val('''|| TABLE_NAME||''', ''' || column_name || ''', ' || ':new.' || column_name || ', :old.' ||  column_name || ')'
from user_tab_columns where table_name = upper('ACCOUNT') AND COLUMN_NAME='ACCT_NM' ;
From the above code i got into another issue i.e I can't able to pass the column name dynamically because :new and :old values are "static" they cannot access "dynamically".

Any idea how to archive the it by the above trigger...

In parallel am implementing oracle Auditing options.

In Auditing options I couldn't able to get the value for above requirement i.e OLD_VALUE,NEW_VALUE,IP_ADDRESS,SOURCE_OF_UPDATE

I have explored the DBA_AUDIT_TRAIL table which has SQL_BIND column. whether i can get the OLD_VALUE,NEW_VALUE from the SQL_BIND?

Is there any idea how to get IP_ADDRESS using oracle auditing?

Thanks & Regards
Sami.
  • 1. Re: DML Trigger for schema
    sb92075 Guru
    Currently Being Moderated
    Sami wrote:

    Is there any idea how to get IP_ADDRESS using oracle auditing?
    is application 3-tier?
  • 2. Re: DML Trigger for schema
    787349 Newbie
    Currently Being Moderated
    Ya. We are using Weblogic10.
  • 3. Re: DML Trigger for schema
    sb92075 Guru
    Currently Being Moderated
    Sami wrote:
    Ya. We are using Weblogic10.
    EndUser<=>browser<=>WebServer<=>ApplicationServer<=>DatabaseServer

    Does application utilize connection pooling?

    only WebServer knows the IP# of EndUser.
    So ApplicationServer must obtain EndUser IP# from WebServer & then pass IP# along to DBServer

    Edited by: sb92075 on May 9, 2012 7:31 AM
  • 4. Re: DML Trigger for schema
    787349 Newbie
    Currently Being Moderated
    Thanks for reply.
    In Auditing options I couldn't able to get the value for above requirement i.e OLD_VALUE,NEW_VALUE,SOURCE_OF_UPDATE
    any idea?
  • 5. Re: DML Trigger for schema
    L-MachineGun Pro
    Currently Being Moderated
    The most information you can get (without modifying your application) is what SYS_CONTEXT() provides, but as sb92075 posted: "only WebServer knows the IP# of EndUser."
    :p
  • 6. Re: DML Trigger for schema
    Justin Cave Oracle ACE
    Currently Being Moderated
    If you really wanted to do something like this, you'd need to dynamically generate the triggers. Something like this (where 'FOO' would presumably be passed on as a parameter)
    SQL> ed
    Wrote file afiedt.buf
    
      1  declare
      2    l_sql_stmt varchar2(1000);
      3  begin
      4    l_sql_stmt := 'create or replace trigger trg_foo ' ||
      5                  '  after update on foo ' ||
      6                  '  for each row ' ||
      7                  'begin ';
      8    for x in (select * from user_tab_cols where table_name = 'FOO')
      9    loop
     10      l_sql_stmt := l_sql_stmt || ' check_val( ''' || x.table_name || ''', ''' ||
     11                      x.column_name || ''', :old.' || x.column_name || ', ' ||
     12                      ':new.' || x.column_name || '); ';
     13    end loop;
     14    l_sql_stmt := l_sql_stmt || ' end trg_foo;';
     15    p.l( l_sql_stmt );
     16    execute immediate l_sql_stmt;
     17* end;
    SQL> /
    create or replace trigger trg_foo   after update on foo   for each row begin
    check_val( 'FOO', 'COL1', :old.COL1, :new.COL1);  end trg_foo;
    
    PL/SQL procedure successfully completed.
    You'd need to generate the trigger every time you added or removed a column from a table.

    A coupld of caveats, though...

    1) Logging every change to any table in a single log table is generally a poor idea-- it makes it incredibly difficult to report on what a particular row looked like at a particular point in the past, it makes the log table gargantuan, and it creates a rather significant bottleneck in your system. Even if you correct the problem that you're not logging the primary key of the table so you have no idea which row of your table experienced any particular update, you'd have to hit the table N times to reconstruct the data that was present for a single row of an N column table at a particular point in time. When the table contains every change to every column of every table, that's going to take forever. When it takes forever for people to run reports, they pretty quickly give up trying to use the audit data.
    You'll generally have a much easier time if your audit table matches the structure of your base table with a couple of extra columns (i.e. a column for the user, one for the date, maybe one for the IP address). Then you simply log each version of the row so you don't generate 10 rows if you update 10 columns and you can always determine what a particular row looked like at a particular point in time by hitting the audit table once. This also removes the bottleneck since your writes are getting sent to a variety of audit tables, not just one.

    2) CHECK_VAL needs to be overloaded to take a large number of different data types, not just VARCHAR2. Think about what would happen if you have a DATE column in a table. There is a row in that table where the value of the DATE column is March 21, 2012 10:24:03. You update the row to have a value of March 05, 2012 10:30:31. If your session's NLS_DATE_FORMAT is DD-MON-RR, the default, no row will be written to the audit table. If your session's NLS_DATE_FORMAT is DD/MM/YYYY HH24:MI:SS, the audit table will have a new value of '05/03/2012 10:30:31'. If your session's NLS_DATE_FORMAT is MM/DD/YYYY HH24:MI:SS, the audit table will have a new value of '03/05/2012 10:30:31'. How would a someone trying to make sense of the audit data know whether an audit row indicates that the date changed to March 5 or May 3 or whether a row was missed because you're doing string comparisons?

    Justin
  • 7. Re: DML Trigger for schema
    787349 Newbie
    Currently Being Moderated
    Justin Thanks for suggestion.
     CHECK_VAL needs to be overloaded to take a large number of different data types
    I can overload the procedure argument..
     Even if you correct the problem that you're not logging the primary key of the table so you have no idea which row of your table experienced any particular update...
    I can pass the PK of the row to AUDIT_LOG table..

    Mean while Is there any way to get BIND VALUES from DBA_AUDIT_TRAIL ?

    Is there any way to get operation Program name ie SQL Plus, Toad, JDBC?


    Thanks & Regards
    Sami.

    Edited by: Sami on May 10, 2012 12:27 PM
  • 8. Re: DML Trigger for schema
    839439 Pro
    Currently Being Moderated
    Is there any way to get BIND VALUES from DBA_AUDIT_TRAIL ?
    No ..

    --neeraj                                                                                                                                                                               
  • 9. Re: DML Trigger for schema
    L-MachineGun Pro
    Currently Being Moderated
    Adding to Justin Cave's recommendation plus my own, here are some scripts you can use to generate the triggers:
    1) Create audit table:
    -- ----------------------------------
    -- cr_audit_table.ddl
    -- 
    -- Create the audit table.
    -- ----------------------------------
    -- 
    CREATE TABLE audit_table
    (  PK_COLUMNS       VARCHAR2(200)   -- Primary key column names
    ,  PK_VALUES        VARCHAR2(200)   -- Primary key value
    ,  TABLE_NAME       VARCHAR2(32)    -- Audited table name
    ,  COLUMN_NAME      VARCHAR2(32)    -- Changed column name
    ,  VALUE_FROM       VARCHAR2(4000)  -- Old value
    ,  VALUE_TO         VARCHAR2(4000)  -- New value
    ,  USER_ID          VARCHAR2(100)   -- Changed by
    ,  DATE_TIME        DATE            -- Change timestamp
    );
    
    CREATE INDEX AUDIT_TABLE_IDX 
    ON AUDIT_TABLE (  TABLE_NAME,  COLUMN_NAME, DATE_TIME);
    2) Create the triggers:
    -- -----------------------------------------------
    -- cr_audit_trigger.sql
    -- Script to generate audit tracking trigger(s)
    -- -----------------------------------------------
    --
    -- Provide the table name as parameter:
    --
    def tab=&1
    
    SET PAGES 0 LIN 1024 TRIMS ON VER OFF FEED OFF ECHO OFF
    --
    -- Generate trigger
    --
    col usr_id new_value usr_id noprint
    col trg_nm new_value trg_nm noprint
    col tab    new_value tab    noprint
    col key    new_value key    noprint
    
    SELECT MAX( DECODE( column_name, 'USER_ID', ':new.user_id', '''USER''' ) ) usr_id
         , LOWER('&&tab._AUR_TRG') trg_nm
         , UPPER('&&tab') tab
      FROM user_tab_columns
     WHERE table_name = '&&tab'
    /
    SELECT   NVL (MAX (DECODE (ct, 'P', column_name))
                , MAX (DECODE (ct, 'U', column_name)))
                key
        FROM (SELECT i.table_name
                   , i.index_name
                   , i.column_name
                   , i.column_position
                   , c.constraint_type ct
                   , ROW_NUMBER ()
                     OVER (PARTITION BY i.table_name, c.constraint_type
                           ORDER BY i.index_name)
                        rn
                FROM user_ind_columns i, user_constraints c
               WHERE i.table_name = '&&tab'
                 AND c.table_name = i.table_name
                 AND c.index_name = i.index_name
                 AND c.constraint_type IN ('P', 'U'))
       WHERE rn = 1
    GROUP BY table_name
    /
    spo trg_&&trg_nm..sql
    SELECT
       'DROP TRIGGER &&trg_nm;'|| CHR( 10 )
    || 'CREATE OR REPLACE TRIGGER &&trg_nm'|| CHR( 10 )
    || '  AFTER UPDATE ON &&tab'|| CHR( 10 )
    || '  REFERENCING OLD AS old NEW AS new'|| CHR( 10 )
    || '  FOR EACH ROW'|| CHR( 10 )
    || 'DECLARE'|| CHR( 10 )
    || '  v_user_id      VARCHAR2(  32);'|| CHR( 10 )
    || '  v_col_name     VARCHAR2(  32) := ''NONE'';'|| CHR( 10 )
    || '  v_ref_values   VARCHAR2(4000) := NULL;'|| CHR( 10 )
    || '  --'|| CHR( 10 )
    || '  PROCEDURE record_change '|| CHR( 10 )
    || '  ( p_column_name VARCHAR2, p_old_data VARCHAR2, p_new_data VARCHAR2 ) IS'|| CHR( 10 )
    || '  BEGIN'|| CHR( 10 )
    || '    INSERT INTO audit_table'|| CHR( 10 )
    || '    VALUES ( ''&&key'','|| CHR( 10 )
    || '             :old.&&key,'|| CHR( 10 )
    || '             ''&&tab'','|| CHR( 10 )
    || '             p_column_name,'|| CHR( 10 )
    || '             p_old_data,'|| CHR( 10 )
    || '             p_new_data,'|| CHR( 10 )
    || '             v_user_id,'|| CHR( 10 )
    || '             SYSDATE, );'|| CHR( 10 )
    || '  END;'|| CHR( 10 )
    || 'BEGIN'|| CHR( 10 )
    || '    v_user_id     := &&usr_id.;'|| CHR( 10 )
    || '  '|| CHR( 10 )
    || '-- ************************************'|| CHR( 10 )
    || '-- NOTE: Remove unwanted columns:'|| CHR( 10 )
    || '-- '|| CHR( 10 )
    || '  '|| CHR( 10 )
    FROM DUAL
    /
    SELECT  
       '  --  '|| column_id|| '. '|| column_name|| ' ( '||data_type ||' ) '|| CHR( 10 )
    || '  IF :new.'||LOWER(column_name)||' <> :old.'||LOWER(column_name)||' THEN'|| CHR( 10 )
    || '    v_col_name  := '''|| column_name||''';'|| CHR( 10 )
    || '    record_change( v_col_name, :old.'||LOWER(column_name)||', :new.'||LOWER(column_name)||' );'|| CHR( 10 )
    || '  END IF;'
    FROM user_tab_columns c
    WHERE table_name = '&&tab'
    AND   column_name NOT IN ('USER_ID','CHANGE_DATE')
    ORDER BY column_id
    /
    SELECT 
    'EXCEPTION' || CHR( 10 )||
    ' WHEN OTHERS THEN' || CHR( 10 )||
    '   NULL;' || CHR( 10 )||
    'END;' || CHR( 10 )|| '/'|| CHR( 10 ) || 'SHOW ERROR'
    FROM DUAL
    /
    spo off
    Good luck! :)
  • 10. Re: DML Trigger for schema
    724567 Newbie
    Currently Being Moderated
    Hi,

    what is happening at line 15 -> p.l( l_sql_stmt ); ??

    ----------
    Thanks,
    Kawa
  • 11. Re: DML Trigger for schema
    Justin Cave Oracle ACE
    Currently Being Moderated
    kawa alkesh wrote:
    Hi,

    what is happening at line 15 -> p.l( l_sql_stmt ); ??
    Oh, sorry about that. I've got a package p with a procedure l that just calls dbms_output.put_line. Saves time when testing things out-- I usually remember to replace that with an explicit dbms_output.put_line before posting it here but it appears I forgot this time.

    Justin

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points