Oldvalue

[heuserj]

I am working on an application that requires different levels of an audit trail. There are 3 levels:

1. Record updates and deletions to the database in the background. I have this set up using the appropriate triggers.

2. The user must select a reason for the update or deletion from a pull down list before the update or deletion is commited. The update or deletion is then recorded in the audit trail along with the reason.

3. Same as #2 but the user must also enter their Oracle password and the password is then verified.

I cannot figure out how to handle settings 2 & 3 with just triggers. I have written stored procedures to write the audit trail records but I am having trouble accessing the old and new values for a field on a form.

Do I need to use cached updates? What is the best event to trigger the needed code? What is the easiest way to get the old and new values and be able to roll back changes if the reason is not valid or the password is not valid?

Thank you very much for your help.

John

 

[Marco Kalter]

It seems to me that level 1 and 2 can be done with database triggers (which is always the preferred method if you ask me). Level 3 seems to introduce an extra application function, because it does not store the password with the audit trail record.

In this situation you do not need an old and new value.

------------------
Marco Kalter
Allround Automations

 

[heuserj]

How would I get the reason into the trigger?

Reason is not a field in the table I am auditing. Any advice would be appreciated.

[This message has been edited by heuserj (edited 08 July 2002).]

 

[Marco Kalter]

You could create a view on this table that additionally introduces the "reason" column. This could be a dummy field in the view's select statement or the actual reason, joined from the audit table.

In an "instead of" trigger you can insert/update/delete the record and perform the audit operation, including the reason.

------------------
Marco Kalter
Allround Automations

 

[heuserj]

Marco:

Thanks alot for your help. This will keep me busy for a while.

Best regards,

John