Hi, boy!
>A work around is you create a routine (timer) to check if the record is deleted. If yes, so change the number for sys(2015).
This is out of the question. I won't install a service just for that.
>Other suggestion: Like you said, you cannot change the record within the trigger... but you can change this value using record validation... One problem: I don't know if record validations is fired automatic, if yes, you will have to create some control to know that operation is a delete!
I thought about that, but there is no way to bind the record validation to a delete. Not that I know of, at least. 8-)
Thanks for the suggestions.
>>This is something that can't happen in SQL Server, but I found in a customer of mine still using DBCs.
>>
>>They have a table with a string candidate key (beside an AutoInc, surrogate PK). The candidate is the number they give to a given type of document.
>>
>>Now, when they delete one of these documents, everything is ok; but when they try to add another document with the same number, they got a 'duplicate key' violation, because the deleted record is still there.
>>
>>I thought about replacing the number with something like sys(2015) before deleting it to avoid collisions, but then I didn't want to put any of this in their user/business logic, but as deep in the DB as possible. The problem is that I can't do it in a DB trigger (I can't update the record within the trigger), and doing two operations at the database logic would mean having to do a bunch of workarounds around the CursorAdapter (which receives a DiffGram from the business tier and access the DB via ADO).
>>
>>I guess this should be something quite common for people using DBCs (not me, obviously). Any idea?
>>
>>Thanks in advance,