Re: Automatic versioning of records to maintain a history

On Aug 28, 2:22 pm, Matthew Williams <matthew.d.willi..._at_gmail.com> wrote:
> On Aug 28, 2:40 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
>
>
>
>
> > On Aug 28, 1:17 pm, Matthew Williams <matthew.d.willi..._at_gmail.com>
> > wrote:
>
> > > Thanks for the help. If I could write this as a Rails app I most
> > > certainly would (with Oracle on the back end) but with the environment
> > > I'm in right now I'm restricted to the Oracle development tools.
>
> > > I'm certainly capable of making the other table and writing the
> > > triggers, but if I can shave development time from this task I would
> > > prefer to do so.
>
> > > I'll give that site you gave me a look, I still might be able to find
> > > a thing or two to help with this task.
>
> > > Take care
> > > -Matthew Williams
>
> > > On Aug 28, 2:04 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
> > > > 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- Hide quoted text -
>
> > > - Show quoted text -
>
> > 'Shaving development time' is one thing, having other people do your
> > work for you under the GUISE of 'shaving development time' is another.
>
> > I have no problem assisting you with this task, but I will not write
> > it for you. The online documentation is an excellent source for the
> > information you need. After you get started writing this if you still
> > have problems post again and I will be happy to assist you.
>
> > David Fitzjarrell
>
> Nope, no troubles. Nor am I looking for someone to write this for
> me. I'm working on it as we speak in fact. I was just looking to see
> if there were any capabilities of 10g that I could set a table to
> automatically have it maintain history. Or any prebuilt frameworks.
> (like the acts_as_version plugin for Rails which I was using as an
> example).
>
> I'm big into Open Source, I try to implement code re-use wherever I
> can. Would be a waste to reinvent the wheel if there was a script to
> automate the task I'm up against already out there floating around.
> Time is money.
>
> Take care.- Hide quoted text -
>
> - Show quoted text -

Oracle isn't open source. So, no, there is no 'automatic magical clairevoyant mechanism in Oracle that knows what you want and gives it to you'. You're on your own, here. Yes, time is money. And if you do this correctly that money will be well-spent.

Again, I have no problems assisting with this, should you run into difficulties.

David Fitzjarrell Received on Tue Aug 28 2007 - 14:43:01 CDT