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.