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,