Re: Date/Time row was modified???

From: Allen Kirby <akirby_at_att.com>
Date: 1997/01/17
Message-ID: <32DF83E3.5A28@att.com>#1/1

Rick,

	First, you're correct.  Oracle doesn't keep track of this
	internally, probably because it would add 8 bytes to each
	row that wouldn't be used by most applications.  So you
	would have to add this column and keep it updated.  We've	
	done it - it's not that hard.  But I don't think you can
	track deletes that way, unless you just flag it and delete
	it later.  Here is an alternative:

	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.

	Hope this helps.

Allen

Rick Stacy wrote:
>
> Hi,
>
> I need to know when rows within my database have last been modified so
> that I can send updates to remote sites using a variety of RDBMS. Based
 <snip>
> Short of adding separate columns to the tables and maintaining the
> information through our application, does anyone have any suggestions
> regarding the best way to handle this requirement?

-- 
---
Allen Kirby			AT&T ITS Production Services
akirby_at_att.com			Alpharetta, GA.

Received on Fri Jan 17 1997 - 00:00:00 CST