Re: Date/Time row was modified???

From: Karel Sprenger <ks_at_ic.uva.nl>
Date: 1997/01/23
Message-ID: <5c7dgo$d4k@mail.ic.uva.nl>#1/1

Hi,

In article <32DF83E3.5A28_at_att.com>, Allen Kirby <akirby_at_att.com> wrote:
>
> Use after insert, update and delete triggers and put the
> primary keys of the records that have been inserted, updated
> and deleted in a new audit table. Have a separate process
> read this table, retrieve the data (for inserts and updates,
> not for deletes) from the original table and perform the
> appropriate update on the remote system. If the remote
> operation is successful, delete the row from the audit
> table. This way you will always know which records have
> been updated on the remote site and you avoid scanning
> the base table for recent updates. This also prevents any
> application changes, and will work with any product (PRO*C,
> Forms, SQL*Plus, OCI, etc.). Just make sure you have
> plenty of space for the audit table. If it can't be written
> to, your updates to the real table will fail.
>

We have been using the same scheme with AFTER triggers (for each row) as Allen suggested for over a year with one of our Oracle databases. Triggers, packages and stored procedures/functions greatly helped us to make this database `self-supporting'. We currently interface it with a NIS+ database on a Solaris system and with a Novell Netware server. Both interfaces emply oraperl scripts that get their data from special tables that are populated by AFTER INSERT, etc. triggers on the `normal' tables.

The only problem would be if one wanted to also use snapshot logfiles to update snapshots in another Oracle database. When creating a snapshot logfile, Oracle also defines an after insert update delete trigger on the relevant table(s) and currently only one type of after trigger is allowed per table.

Regards,

Karel Sprenger <ks_at_ic.uva.nl>

Informatiseringscentrum                     | phone: +31-20-525 2302
Universiteit van Amsterdam                  |        +31-20-525 2741

Turfdraagsterpad 9, NL-1012 XT AMSTERDAM | fax : +31-20-525 2084 *** PGP Public Key available on servers *** | home : +31-20-364 1619 Received on Thu Jan 23 1997 - 00:00:00 CST