Re: Triggers - :new - pseudorecord

In article <2fdbe7f1-46c7-423a-8891-09c44d87a8c5_at_googlegroups.com>, joel-garry_at_home.com says...
>
> On Monday, September 16, 2013 9:35:41 AM UTC-7, jeremy wrote:
> > Hi
> >
> > 11gR2 - have looked at the docs and see that we cannot do within a
> > trigger (assume based on a table called mytab) something like
>
> <snip>
> >
> > Tedious for 120 column tables and repeating similar code for a dozen or
> >
> > so tables.
> >
> >
> >
> > Anyone come across similar requirement and is there any more coding-
> >
> > efficient method?
> >
> >
> >
> > --
> >
> > jeremy
>
> Ah jeez, that programming stuff is so tedious.
>
> Maybe you can find something in http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:208012348074
>

Ideally was looking for a way to do "stuff" with dynamic SQL - know all about arrays and stuff, not sure how that article assists. The objective is, with as little programming as possible (or more accurately let's say modular / reusable), generate records into a "shadow" table which records previous versions of rows from TABLE_A into TABLE_A_SHADOW - we have to repeat this for a number of the most important tables in the system and provide online (web-based) access to super-users to query the shadow tables to determine who/when/what changed.

Original thought was to do this using a bunch of dynamic SQL, generating XML represenatations of the changed records and storing these in a single table. Mladen was a little scathing of this approach (it being a relation databases, designed for tables & rows... the other approach could become very inefficient).

Ultimately just trying to minimise the maintenance overhead when schemas change.

-- 
jeremy