Re: Automatic versioning of records to maintain a history

On Aug 28, 10:33 am, Matthew Williams <matthew.d.willi..._at_gmail.com> wrote:
> I'm working with Oracle 10g and need an instance where whenever a
> record is modified I can automatically store a copy of that record and
> assign it a version number. So at any given time I can see a history
> for that given record in the table.
>
> I'm new to Oracle and I would like to avoid writing as much SQL as
> possible if there is some built in functionality of trigger to do
> this.
>
> The front end of the tool is Oracle Forms.... If this were a Ruby on
> Rails app I could simply use acts_as_versioned on my model and wham,
> I'm all set!
>
> Thanks!

Then write a Ruby on Rails app so, wham, you're all set. Otherwise resign yourself to the 'tedium' of learning how to write triggers to populate history tables. It isn't rocket science. A copy of your source table, with some 'bookkeeping' columns added (such as proc_dt, vers_no, user_id, action) is the starting point; a before insert or update or delete trigger is the next step. I would visit:

http://tahiti.oracle.com

and search the relevant documentation for triggers and learn how such things are written. Should you have trouble you can post the code you've written and we can assist in fixing the errant sections.

David Fitzjarrell Received on Tue Aug 28 2007 - 13:04:52 CDT