File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Oracle/OAS and the fly likes Auditing Table Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Auditing Table" Watch "Auditing Table" New topic
Author

Auditing Table

Ram Ve
Greenhorn

Joined: Jul 08, 2010
Posts: 1
Hi,
I have the following clarification

I have an Employee Table

Employee

Name
Age
Gender
Last_Updated_User
Last_Updated_Time

are the columns in the Employee Table

I also have a generic audit table that I can use with the same format for my application

The Structure of the audit table would be



Column_Name
Old_Value
New_Value
Last_Updated_User
Last_Updated_Time


Expected Behaviour:

Insertion:

1. Whenever a new row has been inserted inside the master table, each column must be inserted as a row in the audit table with the old_value as null and new_value as insertion value


Updation:
1. Whenever a row has been updated inside the master table, each column that is updated only must be inserted as a row in the audit table with the old_value as old value and new_value as updated value

Deletion:

1. Whenever a row has been deleted from the master table, each column that is from the master table be inserted as a row in the audit table with the old_value as last value and new_value as null

How do I achieve this?

Please provide me with a Sample. Am new to Triggers and PL/SQL.

Thanks in Advance








Christophe Verré
Sheriff

Joined: Nov 24, 2005
Posts: 14687
    
  16

Hi, welcome to the ranch You'll find plenty of examples on the net about using triggers. Try to google it first.


[My Blog]
All roads lead to JavaRanch
 
wood burning stoves
 
subject: Auditing Table
 
Similar Threads
Auditing and Logging in EJB CMPs
has anyone encountered this problem when using xpath
NX: Update method
need help in pivot
DB2 700 and 701 - Test Sample