Re: 11gR2 - auditing row changes
Date: Sun, 18 Aug 2013 22:38:50 +0000 (UTC)
Message-ID: <kuridp$q03$8_at_solani.org>
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? 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. 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. 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.
-- Mladen Gogala The Oracle Whisperer http://mgogala.byethost5.comReceived on Mon Aug 19 2013 - 00:38:50 CEST