Interbase problem. Record/Key deleted.

I have Delphi4 installed with Interbase 5.0. Here is the probelm:
I have a database and a table in it. This table is created this way
ID_STOCKGROUP         INT              NOT NULL,
Name                  char(96),
Code                  char(32),
MarkUpValue           float            DEFAULT 0,
MarkUpIsPercent       FT               DEFAULT "T",
MinAmount             float            DEFAULT 0,
MaxAmount             float            DEFAULT 0,
MinMaxWanted          FT               DEFAULT "T",
CassGroup             int              DEFAULT 1,
ID_UserCreate         int,
ID_UserEdit           int
The first field has to be Autoincrement field. But Interbase does not have native support for autoincrement fields. So i made a generator and a triger. The triger fires before post in the database. When i try to post a NEW record i get the BDE error "Record/Key Deleted". After this when i press the refresh button, i see that the record has been posted. But i don't want to get this error. What might be the problem, what does this error meen, and what is the solution?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


I can't give you an exact answer as I don't use Interbase, but I can give you some good pointers.  However the problem is a standard SQL problem when using auto increment fields in the primary key.  

The problem is caused by the fact that you post a record with out a primary key, the DB then gives it a primary key - fine.  However delphi then tries to reposition itself onto that row - probably using the primary key (which as far as delphi is concerned is NULL) and hence cannot find the row. EXCEPTION!

As for a work around - Are you using TTables?  - If so don't.... Use TQuery's.  Also do not request a live result set from the TQuery - use TUpdateQuery to update records.

This should remove the error from the post.  However if you try and edit that row you will probably get the same error.  This may or may not be important.

Should you need to be able to edit a newly inserted record, you have 2 choices - firstly you can refresh the dataset (you may have to close & reopen the TQuery to achive this) - this is fine if the dataset is small.  Alternativly if there is some way of getting the last value of the generator back from interbase then you can put this into the dataset by something like:

Query1.FieldByName('ID_STOCKGROUP').Value := myIdentity;

In MS SQL server after you have issued an insert statement that inserts a row with an identity field you can refer to the value of the variable @@identity which contains the value inserted.

ie you can issue a SELECT @@IDENTITY

You can then put this value into the identity field in your recordset and avoid all complications....

Unfortunately there is no cure all because of the way SQL was defined.

If you want any further assistance, just let me know.

Good Luck


pripkamAuthor Commented:
Sorry, I have forgotten to say that ID_STOCKGROUP has a Primary key, so it seems that this is not the problem.
I use only TTables (not TQuery-s).
About "Query1.FieldByName('ID_STOCKGROUP').Value := myIdentity":
I have already done something like this, and it works fine, but the problem is that i want to move the bussiness ligin in the server. I capture the event lokaly (in the "OnNewRecord" of the TTable), then i call a StoredProcedure which in turn calls the generator, and the proc returns value. That is fine but i want to make this from the server. That is how i will become application-independedable. Do you ged it?


Yes, I assumed it was the primary key as I stated in my answer....  You wouldn't get the error otherwise....

What I suggest is that do everything server side, but you then need to get the value that got inserted into the primary key.  As I said under MS SQL you can use SELECT @@IDENTITY - I assume you can get in effect generator - 1 - or perhaps return the value of the primary key from your insert stored proc (my prefered route).  

You then need to put this value into your local recordset manually as delphi doesn't know about this because it happened server side.  Hence the code sample above.

I think you misunderstood my answer - Basically you need to insert the value for the primary key into the delphi field *AFTER POST* so that the delphi cached recordset reflects that of the live data.  Otherwise you get the dreaded record deleted error which is cause by delphi realising that its cached record and the one on the server are different (not surprising - the server added a field...)

Unfortunately there is no *nice* work around - this is as good as it gets.  You either refresh the dataset or you pick up the identity value and "kludge" it back into the cached dataset.

It isn't ideal but it is the main problem with the SQL standard.  As the changes are heppening remotely you have *NO* way of monitoring them - so you have to deal with it manually at the client or refresh the entire dataset (how can you refresh the row if you don't know its primary key?).

The solution I use under SQL server is to use a stored proc to do the insert and return a result set from the proc that contains the value of the indentity field I have just inserted.  I then force this back into the dataset.

Good luck,

Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

pripkamAuthor Commented:
It seems that i am missing something. PLEASE post some code here so that i can understand you. I  THINk i did what you said, but i am not shure.
Sorry about this INCONVENIENCE

pripkamAuthor Commented:
It seems that i am missing something. PLEASE post some code here so that i can understand you. I  THINk i did what you said, but i am not shure.
Sorry about this INCONVENIENCE


Unfortunately code might be a bit dificult - I don't use interbase - in fact I don't even use the BDE I use some TQuery descendants for direct MS SQL access however the same problem applies.

Okay here goes.....  This is a kludge but it should work.....

1) Declare a boolean variable (called insert in this example).
In you AfterInsert event set insert := True (obviously in  oncreate set it to false).

In the AfterPost event write something like this...

  if insert then
    insert := false;
    SQLQuery1['p_campaign'] := GetIdentityValue;

This code works here with SQL....  However it does assume that your update SQL objects update query does not touch the identity field.

The GetIdentityValue function returns the value that was inserted into the identity field from the DB.

What the above code does it force delphi into putting the correct value of the identity field into the cached copy of the identity field.  

You will find that you can also now edit a newly inserted field etc.

If you want any further information just let me know

Good luck


pripkamAuthor Commented:
Sorry for the delay. (problems with the ISP)
So here is what happens. I desided to reorganise everything. First i started using TTables. From the "Client/server programming with Delphi3" i took a table that is absolutly the same as mine table(if you want i can mail the tables to you). It has triger like mine, generator like mine, primary keys and so on.... Here comes the intereting part: It works with their table but doesn't with mine, that is when i try to post a new record, the record is been posted and updated in the database, but i get the error "Record/key deleted" again. I don't get this error with thier table. I  comapred the two tables field, by field, property by property...
Absolutly strange.
Sorry about the reeling out :)

Well lets put it this way the SQL standard states that this should *NOT* work.  I have given you the answers above which is the accepted method of getting the identity for a table after insertion.  Unless borlands example performs a refresh (or uses some cunning interbase feature) it should not work.  SQL servers will not work in this context as explained earlier.  According to the SQL standard when you perform an insert statement the server does not tell you the value of the identity inserted therefore your program does not know what the identity value is - it has *NO* way of knowing what it will be.  Consequently when you access the table you get an error due to inconsistancies between the cached record set and the one on the server.....

As a note, if you are using a database server you should be using TQuery objects, not TTable objects.

The last answer I gave is a valid work around, and is the only portable method I know of.  I have had it working with differing databases without a problem.  If you are looking for a miracle solution that involves no coding then unfortunately I cannot help you.



The Record-Deleted-Problem is a well known when using the BDE with an SQL-Server. It happens when the SQL-Server modifies the primary key within the before insert trigger but the BDE cannot know the new value so it thinks the record has been deleted.
also see:
AFAIK, no SQL Sever has a datatype like Autoincrement, because it is not needed. They have triggers and generators instead.
So your first approach was totally correct. Use TQueries with SQL-Servers.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pripkamAuthor Commented:
Here go the points...
But why should i use TQuery instead of TTable?
Is seem that my database was currupted, because when i rebuild it(entirely new one, but the same as first one) it is working ok with TTable.
tTable's are not designed to work with SQL-Servers. They hide operation from the user (like select * and other nasty things).
If you go from local to remote you'll see that you'll have to minimize network traffic.
I have had this problem for years with no solution with Interbase/Delphi/BDE. What Zac and EmmDieh make sense if you are modifying the primary key. But what if you are not?

I have an interbase table where I'm changing a non PK/non indexed value on an exisitng record (i.e. *NOT* a record that I recently inserted). For many years this record was modifyable now: RECORD/KEY DELETED.

Anybody got any ideas?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.