Re: 11gR2 - auditing row changes

From: Jeremy <jeremy0505_at_gmail.com>
Date: Mon, 19 Aug 2013 09:51:06 +0100
Message-ID: <MPG.2c7becda2a595a1e989722_at_news.individual.net>



In article <kuridp$q03$8_at_solani.org>, gogala.mladen_at_gmail.com says...
>
> On Sun, 18 Aug 2013 18:37:49 +0100, Jeremy wrote:
>
> > 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.
>
> This is why relational databases should be kept out of reach of children,
> developers and magazine reading executives. You want to create an XML
> document? What's wrong with a relational table?

Because we would like a low-maintenance, generic solution. If a column is added to a table it would be nice to not have to code this into the "shadow" record and modify all DML.

> You can easily dump it to
> XML using something like this:
>
> http://mgogala.byethost5.com/dump2xml.zip
>
> It's written using the practical extraction and reporting language.

We have already developed utilities which generate a specified row (or rows) from any table into an XML representation using dynamic SQL (this is part of a utility which allows individual "seed data" rows to be transferred between cloud systems to effect upgrades via web services).

> In an
> attempt to create a fancy application, using XML and clould, you'll end up
> with a convoluted monster which will perform poorly and be a nightmare to
> maintain.

It's possible; everything is prototyped and impact on system performance assessed prior to full development. If it isn't the right technical approach, it will be dropped.

> XML is just a way of presenting data. Relational data should be
> kept in tables with rows, indexes and primary keys and only presented as
> XML as requested.

Fully understood and recognise that XML isn't the answer to every problem.

We're fully prepared to implement what is the best strategic solution - providing it allows us to easily identify who changed what columns and when for specified tables.

Idea#2 was to have for the required tables a "shadow" table wih the same structure but which holds the before image of the rows. For this I think we need to create a trigger on every table we need to audit and pass a rowtype into a package procedure to handle the creation of the "shadow" row. This is slightly more maintenance but probably quicker to implement.

For idea#2 (selecting rows from a specific shadow tables) any suggestions on the kind of query that would enable just columns whose values had changed across time to be exposed?

-- 
jeremy
Received on Mon Aug 19 2013 - 10:51:06 CEST

Original text of this message