Prev: Oracle Error 600, [733] ...
Next: Cycle thru columns in table?
From: Rich on 24 Feb 2010 19:51

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?
From: Carlos on 25 Feb 2010 04:00
On Feb 25, 1:51 am, Rich <richma...(a)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?

Isn't it the good ol' "journal"?

Cheers.

Carlos.
From: Mark D Powell on 25 Feb 2010 09:42
On Feb 24, 7:51 pm, Rich <richma...(a)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 --




 | 
Pages: 1
Prev: Oracle Error 600, [733] ...
Next: Cycle thru columns in table?