DBAsupport.com Forums - Powered by vBulletin
Results 1 to 1 of 1

Thread: DB Auditing using triggers

  1. #1
    Join Date
    Mar 2005
    Posts
    143

    DB Auditing using triggers

    I am trying to set up database auditing. I am using triggers to write to an audit table when an action happens on a certian table. Here is my situation. I want to audit the ps_addresses table in my 9i DB. I want to audit the actions update, insert and delete. The customer wants to see the old address as well as the new address. When I do a delete on the ps_addresses table it shows the old data (customer is happy), when I do an update on the table I get the old data and the new data( the customer is happy again) when I do an insert on the table I get only the new data, no old data (the customer is not happy). Here is what I think when you do an insert there is NO old data to show. But the customer says I am wrong. If the employee TOM has 2 row in the table like this
    emplid address effdt
    11718 10 somewhereville 2-JAN-2004
    11781 1 parts unknown 3-SEP-2005
    he originally lived in somewhereville then moved to parts unknown. If I insert another row the customer wants to see the new row I just inserted and the old row. CAN this be done? Is the customer right? I have attache the code for the trigger I am using to run the audit it is the file called trigger1. When I ran use that trigger everything works but I get the results I stated in the begining of the post. I tried to modify the trigger to give me the old value when the insert action is performed and I get the following error message:
    ORA-01400: cannot insert NULL into ("SYSADM"."PS_AUDIT_ADDRESSES"."EMPLID")
    ORA-06512: at "SYSADM.ADDRESSES_TR", line 9
    ORA-04088: error during execution of trigger 'SYSADM.ADDRESSES_TR'
    the code for the trigger that is giving me the error is called trigger2.
    Attached Files Attached Files

Posting Permissions

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


Click Here to Expand Forum to Full Width