Re: Automatic versioning of records to maintain a history

<sybrandb_at_hccnet.nl> schreef in bericht news:it39d3t14q2n951eqf7aiumr9phvpubd7j_at_4ax.com...
> On Tue, 28 Aug 2007 15:33:46 -0000, Matthew Williams
> <matthew.d.williams_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!
>
> In the past Oracle Developer was capable of generating these history
> tables.
> Oracle Enterprise Edition has a feature called Oracle Workspace
> Manager.
> Examples probably on http://www.psoug.org/reference
> (Morgan's library)
>
> I have developed to a tool to generate those tables and triggers.
> As I am not in the office I don't have access to it know.
> It is pretty generic, and you end up with a set of pl/sql with the
> table definition being referenced as a subtype.
> Which means: any table Alteration and you only need to recompile (ie
> not change) that piece of PL/SQL.
>
> --
> Sybrand Bakker
> Senior Oracle DBA

Actually it was (and is) Oracle Designer which is capable of generating history tables, or as they call it: journalling tables and triggers to populate them. You could reverse engineer your tables to Designer and generate the trigger code and journal tables. But if Sybrand's tools work (and I don't doubt they do!) you should use those!

Shakespeare Received on Wed Aug 29 2007 - 13:56:56 CDT