Re: 11gR2 - auditing row changes
From: Jeremy <jeremy0505_at_gmail.com>
Date: Sun, 18 Aug 2013 18:37:49 +0100
Message-ID: <MPG.2c7b16c7e4c86412989721_at_news.individual.net>
In article <pan.2013.08.18.15.05.06_at_gmail.com>, gogala.mladen_at_gmail.com says...
>
> On Sun, 18 Aug 2013 12:20:29 +0100, Jeremy wrote:
>
> > Hi we are running 11gR2 standard edition.
> >
> > A business requirement has emerged which means that potentially a user
> > would like to be able to see what columns have cheanged on each update
> > to a row in one of several tables.
>
>
> You can create a trigger and create your own audit table. You can also
> use system auditing, in conjunction with the Log Miner, which is by far
> the most complex method. You can also use virtual private database.
Date: Sun, 18 Aug 2013 18:37:49 +0100
Message-ID: <MPG.2c7b16c7e4c86412989721_at_news.individual.net>
In article <pan.2013.08.18.15.05.06_at_gmail.com>, gogala.mladen_at_gmail.com says...
>
> On Sun, 18 Aug 2013 12:20:29 +0100, Jeremy wrote:
>
> > Hi we are running 11gR2 standard edition.
> >
> > A business requirement has emerged which means that potentially a user
> > would like to be able to see what columns have cheanged on each update
> > to a row in one of several tables.
>
>
> You can create a trigger and create your own audit table. You can also
> use system auditing, in conjunction with the Log Miner, which is by far
> the most complex method. You can also use virtual private database.
Hi trigger and own code utilities was 1st thought. We were thinking to generate (via dynamic sql) an XML document for the current row and then store this in a table with some other reference columns such as AUDIT_ID, TABLE_NAME, PK_ID, DATE_UPDATED, USER_ID_UPDATED. Anyone tried something similar? Trying to do as much as possible with dynamic SQL with a view to making generic utilities.
2nd part of the problem is how to query/compare each row with its previous incarnation to compare for changes.
-- jeremyReceived on Sun Aug 18 2013 - 19:37:49 CEST