Hi Martín...

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).

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!

[]s!


>Hi, all.
>
>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,