Hello Again Walter

This seems to be the thread that never dies:)

Some months back I adopted a technique you described of having an index on deleted() and it has proved very successful for me and has presented no performance issues at all...thanks...I can always depend on you to cut to the centre of my problem.

However looking at some of your recent postings on it I am wondering if you have changed your position on using Deleted().

If deleted() is likely to cause performance issues on big tables then perhaps the method for recycling deleted records I was toying with would work.

Using this method I would have a new counter called PK_DELETED.

This counter would return negative numbers which would replace the PK for any deleted record in any table.

The advantage of doing this rather than just reversing the original PK is that records can be appended from other tables even if these other records contain a valid PK.

The only drawback with this method I can see, is that all deletions must be done with a function to update the PK of the deleted record.

Is it possible that that this can be done with a Trigger?

Since the delete command is not used that much in most apps I do not see this as adding too much overhead, compared to the savings to be gained from recycling deleted records.


Since this is a somewhat dramatic change of direction for me to make I would welcome any comments you may have.



>
>I will try what you suggest, it certainly does seem like a simple method (therefore probably the best).
>
>However I wonder if the fact that I now have 2 records with the same PK in my table (albeit one is deleted) is ever going to cause me problems in the future.
>
>In the method I suggested earlier, I had spotted the problem of deleting a record twice as soon as I had sent the message to UT :(
>
>However it occurs to me that this problem can be avoided by using a new -minus based PK key.
>
>So in my getkey tables I have a minus key KEY_DELETED and I use this key to replace the PK in any record I delete.
>Obviously I would have to do all deletions via a function and this would slow deletions down.
>
>The way you mention is superior if I can be sure I will not run into trouble with the technique.
>
>Many Thanks
>
>
>
>>Hi mark,
>>
>>I don't think your proposal is neccesary and might cause problems when deleting a certain record twice.
>>
>>If in your case, the deleted records in the target table is the only problem, I think by filtering the PK on deleted() will solve your problems nice and clean.
>>
>>Just give it a test: Open the target table exclusively, open it in de table designer (MODIFY STRUCTURE), go to the indexes tab, select the primary key, and type: !DELETED() in the filter column.
>>
>>For rushmore optimizations reasons, add another regular index on the primary key: (For example: INDEX ON MyPrimaryKey TAG PK2 )
>>
>>No just try what you're trying to do again.
>>
>>Hope this helps.
>>
>>Walter,
>>
>>>How does this sound as a solution to my updating problem using surrogate keys.
>>>
>>>To avoid getting an update problem I convert the PK in my target table to a minus amount. I then delete the record and cascade any deletes that are neccesary if more tables are involved.
>>>
>>>Because the deleted records in the target table now have a different PK I can append in my new records (which contain the original positive PK)
>>>
>>>I realise that this can be a bit inefficient in some circumstances but I can think of a lot of circumstances where it would be the simplest way.
>>>
>>>Assigning a minus PK to deleted records would also have the advantage of allowing me to start recycling deleted records again like I did in the old days.
>>>
>>>Am I about to make a big mistake .
>>>
>>>Please let me know
>>>
>>>Thanks:)