Re: Comparing two rows
Date: Thu, 25 Feb 2010 06:42:08 -0800 (PST)
Message-ID: <5fa08bb2-4d7c-4833-aa45-65fe94787499_at_d2g2000yqa.googlegroups.com>
On Feb 24, 7:51 pm, Rich <richma..._at_earthlink.net> wrote:
> I am using 10g.
>
> I have an audit trail stored procedure to create. There is a customer
> table. Then customer_audit table. Customer and Customer_audit tables
> are identical. Each time a record is changed, a copy of the old record
> is sent to Customer_Audit.
>
> My report needs to compare the record in the customer table verses the
> one in the audit table.
>
> I basically want my code to go field by field and spot the
> differences. I intend to send differences to a table.
>
> I know that I can do this with cursors. Is it possible to do this with
> an SQL statements?
You can use an after update table row trigger to capture rows that have changed and determine what columns were changed at the time you write the history. Otherwise you would have to write code to do a column by column check.
With 11g there is the flashback archive feature to automate the capture of change row data however it does not identify the column changes. You would still need to write code to do that.
Some sites instead of writing the entire row to history/audit write only the change in the format of column_name, old_value, new_value and likely who performed the change on what date.
I prefer to write the entire row.
For reporting the Mark 1 Eyeball can usually spot the differences between rows well enough that the changes to not need to be identified in code. When you have to identify the differences outside of the trigger then the only practical method I can think of is to IF test the history row columns against the base row columns one after another. If you write 100% of the data to history you can perform the work just using the history (also called audit or change) table for the source of the report.
HTH -- Mark D Powell -- Received on Thu Feb 25 2010 - 08:42:08 CST