Link to home
Start Free TrialLog in
Avatar of sam15
sam15

asked on

Auditiing_Data.

I want to use this sample trigger to audit iupdates and deletes to a table EMP.

So i create my new audit table with extra columns to know WHO, WHAT and WHEN.
I am logging in the whole record into this audit table.


As i am analyzing this, i think there might be something wrong with logging the values for those extra columns for UPDATE since i am copying the old values of the record. The user name, ip, terminal is not
what created the audited row. The user did the updates stored in the base table and not this audited row.

Is this correct and how do I associate the old audited row values with the original user info?

*******************************************************************
CREATE OR REPLACE TRIGGER TRG_EMP_AUDIT
 AFTER  DELETE OR UPDATE ON EMP_TAB
 FOR EACH ROW DECLARE
 
 v_operation   VARCHAR2(10) := NULL;
 v_user        VARCHAR2(20);
 v_timestamp   Date;
 v_ip_address  VARCHAR2(25),
 v_terminal    VARCHAR2(10);

BEGIN
 
 v_user := USERENV(user);
 v_timestamp := SYSDATE;
 v_ip_address := USERENV(ip_address);
 v_terminal := USERENV(terminal);
 
 IF UPDATING THEN
   v_operation := 'UPD';
  ELSE
   v_operation := 'DEL';
  END IF;
 
 
   INSERT INTO EMP_TAB_AUDIT (
    seq,
    operation,
    user
    timestamp,
    ip_address,
    terminal,
    empno,
    job,
    mgr,
    hiredate,
    sal,
    comm,
    deptno )
   VALUES (
     audit_seq.nextval,
     v_operation,
     v_user,
     v_timestamp,
     v_ip_address,
     v_terminal,
    :old.empno,
    :old.job,
    :old.mgr,
    :old.hiredate,
    :old.sal,
    :old.comm,
    :old.deptno);
   
  END;
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

>> v_user := USERENV(user);

What version of Oracle are you running?  This does not even compile for me in 10g.  The syntax is incorrect.

USER is not a valid parameter for USERENV in 10g:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions208.htm

You should be using SYS_CONTEXT:
      SYS_CONTEXT('USERENV', 'SESSION_USER')

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions165.htm#i1038176


As for the incorrect values:  Are you running a N-Tier client server app where users log into the application and the application uses a common user to log into the database?  Such as Peoplesoft?
Avatar of sam15

ASKER

It is 9i but dont worry about the user environment variables.
There is one web application and one client/server application that user the database. we also use one database account now so i need to capture the O/S or machine name.

But the issue is it seems i may have to end up adding those fields to the base table (not audit table) so it gets copied to the audit table with the original record because iam copying the old values. If i am copying the NEW values it would work this way but then there are other issues is that i have to audit INSERTs and copy all the records in the existing table.
I do not understand the new versus old issue.   For example,on update, if the env can caputure the user ip address, if the column is in the origianl table or the audit table shold not matter.  Can you explain a little more?
Avatar of sam15

ASKER

What i am saying is that let us say USER JOHN updates Record 1 in Table 1.
Now the trigger will copy old data for Record 1 to  EMP_AUDIT table. The new/current record is stored in EMP base table.

All the ENV variables i store in audit table (i.e ipaddress, machine name, OS username) in the audit table do not really belong to the record stored in audit table. These belong to the new record in base table.

If i  want to store the OLD in audit table and all these ENV variables it means I have to add those fields to the original EMP table so the trigger copies those with each record and I need a trigger on the base table to update those values in the base table.

Do you see the issue now?
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sam15

ASKER

On option #1 why create another table, you can add the audit columns (ip, machine name, updated_by) to the base table and it will be copied to the audit table with every row. But i need to add these audit columns to every base table plus i need a trigger to populate so ther is some work involved.

On option#2, i decide to go with the shadow table approach and copy the entire record. There are 100 column tables and this is the easiest instead of writing ton of code to derive the chanegs only with every DML.

On Option#3 (the ugly), i was thinking about this and i think i will do this.
I am not sure why you are calling this ugly. The only draw is "storage" and i do not have millions of records. They are pretty small tables.
That way i will have all the history data in one table instead of joining to the base table for the current one. basically I want to audit INSERT (capture :NEW) and audit UPDATE capture :OLD) and audit DELETE (capture :OLD).

Is #3 bad solution if you dont have ton of data? The only disadvantage is duplicate row in audit table.
#1:  If you can add columns to the base table, then this would be my choice.  The problem wit messing with base tables is it can break code.  If you had 'bad' programmers and they did things like:  select * from emp into id,name;  and you add a new column, the code will break since the INTO doesn't have the new columns.

#2:  It appears you already understand the problems here.

#3:  I guess it depends on how much of your data is updated over time.  For my system, very few updates occur.  Mostly inserts.  Why duplicate an entire table for less than 1% total updates.  My update 'audits' are rare.

If you will liekly have a high percentage, then it might not be an issue for you.

Then main issue is reporting on 'changes'.  Since the audit table has an initial copy, you will need to filter out actual 'changes' (likely the username is NOT NULL) but you neeed to account for it.
Avatar of sam15

ASKER

for reporting, I am thinking of creating a VIEW with LAG() function that comapares each row to the previous one and it is same value it would show NULL and if value changed it would show the new value.

I think that should be straighforward.