INCREMENTAL DATA EXTRACTS FROM TABLES

From: <djose_at_att.com>
Date: 1997/06/17
Message-ID: <866566437.16309@dejanews.com>#1/1

Hi everyone,

PROBLEM:

---

I have 2 tables. Parent table and child table with a 1-m relationship.

	ORDERS
		cust_cd		key-1
		order_date	key-2
		update_date	( date on which inserted/updated )


	ORDER_DETAILS

		cust_cd		key-1
		order_date	key-2
		line_no		key-3
		update_date	( date on which inserted/updated )

I have a Pro*C program which downloads data into a particular format from both these tables.

I need to pull out data from parent and child tables which have changed since the last download process.

I cannot use export or sqlloader since there are a lot of formatting gimmicks
and some minor logic which the Pro*C program does.

2 SOLUTIONS I HAVE THOUGHT OF:

---

1. Solution-1

   ---

        Have a trigger on ORDERS and ORDER_DETAILS table which fire and populate

        a third table EVENT_LOGGER with keys from ORDER and ORDER_DETAILS.

	Have a Pro*C process which pulls out keys from event-logger , goes
	against the ORDERS and ORDER_DETAILS and produces the flat file.

	Cons


	----


	a) Overhead on trigger getting fired


2.	Solution-2


	----------


	Everytime the download is done store the date somewhere and
compare it
	with  UPDATE_DATE of each row before deciding whether to write to
flat
	file.


	Cons


	----


	a) Deleted cant be recorded.


MY QUESTION  [  at last :-)                  ]

---

1. Have any of u experimented / tried out any other mechanisms.
2. Can you add to the Pros / Cons list for each of the above 2 solutions.

I would very much appreciate any help from all ye netters.

Thanks for your time.

regards,
Jude.

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet

Received on Tue Jun 17 1997 - 00:00:00 CDT