Re: Help needed on Ora Forms

are you trying to "journal" changes?

That is, are you trying to keep track of who inserted, updated, or deleted what, and if updated, what was the value before it was updated?

if so, one relatively easy method for doing this is a database trigger that acts on insert, update, and delete and writes a copy of the current record into a separate, journal table, that looks very much like the table you are tracking. Additional columns in the journal table would contain the date/time of the transaction (JN_DATE_TIME DATE NOT NULL) , the user who made the change (JN_USER VARCHAR2(30)), and the kind of change (INS, UPD, DEL) (JN_TYPE VARCHAR2(3)). Columns identifying the application making the change (JN_APPL VARCHAR2(30)) , and maybe some notes or comments would be useful, too. (JN_NOTES VARCHAR2(240))

One variation of this is to make all columns in the journal table character (VARCHAR2) with a max length at least one column longer than the longest value ever entered into the source table. The extract character is for an * to mark the column or columns begin changed in an UPDATE transaction. Another, option would be to include update flag columns in the journal table for all columns, and the trigger would set the flag when the associated column is updated.

If you track, in a journal table, all changes to your records, then reconstructing the history of a record is easy. You search the journal for the specific primary key of the record being "audited". Sort them by the journal date/time (JN_DATETIME), and march through the various transactions, from the inital insert, through all the updates -- looking for columns flagged as changed -- and then to the final delete.

hope this helps...

"aha" <aha2000_at_mail.com> wrote in message news:5e6c1f32.0212040950.71497caf_at_posting.google.com...
> Is there a better way to meet the following requirement?
>
> 1. user navigates through a table,
> 2. user changes some value in a field for a record,
> 3. the "updated" record is INSERTED into the table,
> 4. the original record is updated, but the field in question remains
> the same, instead, another field gets changed.
>
> I think I have to use control block to do this, but that makes the
> navigation difficult.
>
> Hint?
Received on Mon Dec 09 2002 - 08:29:15 CET