Re: Removing dublicate records in table and hostarray

Hi

Why not have a before insert trigger for each row on table x which is fired before a transaction is inserted.

The trigger code will open an explicit cursor which checks for a similar record in table y, Use the predefined exception WHEN NO_DATA_FOUND to allow the insert to proceed else exit from trigger without updating table x.

Nice and clean no clean up of table x .

Colin.
Henk de Wilde wrote in message <3542586a.11559263_at_news.xs4all.nl>...
>On Fri, 24 Apr 1998 13:29:49 +0200, Brian Rasmusson <br_at_belle.dk>
>wrote:
>
><SNIP>
>>
>>It would be a hassle to clean up the transaction table then..
>>
><SNIP>
>
>Why don't you separate the check, the transfer of the valid records
>and the cleanup in tree separate steps?
>
>Add a column STATUS NUMBER _Nullable_ to your scratchpad table X.
>Take care that noone but you ever touches it. The filling applications
>leave this field NULL so unprocessed records can always be recognized.
>
>Now you do the check by updating STATUS
>
>UPDATE X
> SET X.STATUS = ( SELECT COUNT(*) FROM Y
>WHERE Y.Key_1 = X.Key_1
> AND Y.Key2 = X.Key_2 etc... )
>Assuming that you have an index on all relevant fields off course.
>
>After this the rows that have no duplicates have a zero in field
>STATUS, so for your transfering application you can now select all
>rows with STATUS = 0. In the mean time those other applications can
>continue filling table X with more records, because they will be
>neglected until they in turn have been checked.
>
>When all rows have been transferred you can do a simple cleanup with :
>DELETE X WHERE STATUS IS NOT NULL;
>
>Both the processed rows with STATUS = 0 and the duplicate rows with
>STATUS = 1 will be deleted by this statement, without ever touching
>the new rows that might have been inserted in the meantime.
>
>You might even beforehand do a cheap check on your Y table with
>SELECT COUNT(*) FROM X WHERE STATUS > 1;
>This should never return anything but zero.
>
>
>I hope this helps
>
>Henk de Wilde.
Received on Mon Apr 27 1998 - 05:13:58 CDT