rmk
asked on
cannot delete rows with unknown keys
In SQL Server 2000 SP 3A, I have a table with an identity primary key. The insert trigger joins the inserted table with 3 other tables and then does inserts into a child table that also has an identity column as its primary key. If I open the table in Enterprise Manager and key in the values for a new row, the row gets inserted and the trigger correctly inserts rows into the child table. However, the value of the identity primary key for the parent row I just entered does NOT appear in the grid. If I try to delete the row in the grid I get the "cannot delete rows with unknown keys" error. If I refresh the grid, the primary key appears and I can delete the row. If I remove the trigger inserts into the child table, the primary key of the inserted row appears in the grid as expected. This is killing me because the data entry form is in an an Access XP ADP project where the unknown value for the primary key is killing me. FYI, here's the guts of the insert trigger.
INSERT tblAmenitySpecFlg
(
AmenityID , AmenityTypeAttrDefID , Flg
)
SELECT i.AmenityID, atad.AmenityTypeAttrDefID,
FROM inserted i INNER JOIN tblAmenityTypeAttrDef atad ON i.AmenityTypeID = atad.AmenityTypeID
INNER JOIN tblAmenityAttrDef aad ON aad.AmenityAttrDefID = atad.AmenityAttrDefID
INNER JOIN tblAmenityAttrDefType aadt ON aadt.AmenityAttrDefTypeID = aad.AmenityAttrDefTypeID
WHERE aadt.Code = dbo.udfAttrDefTypeCodeFlg(
I can't duplicate the problem when I create a trivial parent table with a trigger that inserts into a trivial child table. HELP !!!!!
INSERT tblAmenitySpecFlg
(
AmenityID , AmenityTypeAttrDefID , Flg
)
SELECT i.AmenityID, atad.AmenityTypeAttrDefID,
FROM inserted i INNER JOIN tblAmenityTypeAttrDef atad ON i.AmenityTypeID = atad.AmenityTypeID
INNER JOIN tblAmenityAttrDef aad ON aad.AmenityAttrDefID = atad.AmenityAttrDefID
INNER JOIN tblAmenityAttrDefType aadt ON aadt.AmenityAttrDefTypeID = aad.AmenityAttrDefTypeID
WHERE aadt.Code = dbo.udfAttrDefTypeCodeFlg(
I can't duplicate the problem when I create a trivial parent table with a trigger that inserts into a trivial child table. HELP !!!!!
"cannot delete rows with unknown keys" You need to refresh the grid and the id will show and this problem will go away
does dbo.udfAttrDefTypeCodeFlg(
reference tblAmenitySpecFlg in any way?
IF so the commit hasn't happened yet and the row won't have the key defined yet.
what excactly does the udf return?
there are no args to the udf so does it return the same value all the time or does it get information
from some table also used in the insert trigger?
reference tblAmenitySpecFlg in any way?
IF so the commit hasn't happened yet and the row won't have the key defined yet.
what excactly does the udf return?
there are no args to the udf so does it return the same value all the time or does it get information
from some table also used in the insert trigger?
ASKER
The function just returns a literal constant. It's my way of creating a global constant that can be changed in one place. In this particular case the function returns the value "FLG".
Refreshing the grid is not a viable option. The unknown value causes an error in my form.
Refreshing the grid is not a viable option. The unknown value causes an error in my form.
"Refreshing the grid is not a viable option" it is the only option. This is enterprise manager It is how it works. It queries the database and thats it, until yo tell it to query it again!
ASKER
I'm using Enterprise Manager just to demonstrate the problem. My application is an Access XP ADP which is exhibiting the same behaviour and I don't know of a way to refresh the grid and identify the last inserted record especially in a multi user environment.
The issue with Access is exactly the same issue. Both applications issue a query and display the results. when you type i na new record there is no "Loopback" which returns the new ids of rows inserted the only way to acquire them is by a requery.
ASKER
I beg to differ. I do lots in inserts in Access forms and never have this problem.
>>I do lots in inserts in Access forms and never have this problem.<<
They have Triggers in MS Access?
They have Triggers in MS Access?
Speaking of MS Access please maintian these abandoned questions:
1 09/17/2004 250 Viewing Linked Objects In A Form Open Microsoft Access
2 10/15/2004 250 How to Enumerate Tables and Columns in a... Open Microsoft Access
1 09/17/2004 250 Viewing Linked Objects In A Form Open Microsoft Access
2 10/15/2004 250 How to Enumerate Tables and Columns in a... Open Microsoft Access
ASKER
I am using an Access ADP, not an MDB. Therefore I have a direct connection to SQL Server tables, views, stored procedures etc. I have many instances where bound forms insert rows into a table with an identity primary key and everything works just great. I have many more instances where bound forms insert rows into a table with a an identity primary key where that table has triggers that insert rows into other tables with identity primary keys. That's why I'm so shocked and puzzeled as to why SQL Server / Access are having a problem in this case.
>>That's why I'm so shocked and puzzeled as to why SQL Server / Access are having a problem in this case.<<
I know it happens to us all, then we get over it and find a workaround ...
I know it happens to us all, then we get over it and find a workaround ...
ASKER
Short of tons and tons of work to convert to unbound forms, I'm still totally at a loss for finding a workaround.
My knowlege of access is somewhat limited so forgive me for picking ideas out of my head here but are you using the latest MDAC ?
ASKER
I have installed every Windows, SQL Server, and Access update available. I have the same problem on both a Windows XP Pro box and a Windows 200 Pro box. I've even implemented a SQL Server hot fix for a bug that Microsoft fixed for me several months ago and has not yet issued in a service pack. The problem exists with and without the hot fix.
I'm really stuck because once the error occurs in Access, the only thing I can do is use Task Manager to terminate the application - not exactly an elegant user experience.
I'm really stuck because once the error occurs in Access, the only thing I can do is use Task Manager to terminate the application - not exactly an elegant user experience.
Hmm. Much as I hate to say this but I dont think i can help any further with this one. My Access skills are not what they one were.
But why do you need to terminate the application ?
But why do you need to terminate the application ?
ASKER
Once Access generates the error message and returns me to the form, the form is in a dirty state. Something else bizarre has happened under the covers because I would normally just hit the escape key a couple of times and then move on to some other research steps. However, hitting the escape key generates other errors and I can't get out of the error loop.
Thanks to one of my local SQL guru buddies, I temporarily changed the primary key of tblAmenitySpecFlg (the child table the trigger is inserting into) to a non identity and tweaking the trigger to force it to insert a primary key value. Amazingly, everything works just fine. So I'm guessing that somewhere SQL Server is getting confused between it's own internal use of @@IDENTITY and @@SCOPE_IDENTITY. Despite this interesting revelation, I'm still searching for a workaround.
Thanks to one of my local SQL guru buddies, I temporarily changed the primary key of tblAmenitySpecFlg (the child table the trigger is inserting into) to a non identity and tweaking the trigger to force it to insert a primary key value. Amazingly, everything works just fine. So I'm guessing that somewhere SQL Server is getting confused between it's own internal use of @@IDENTITY and @@SCOPE_IDENTITY. Despite this interesting revelation, I'm still searching for a workaround.
>>SQL Server is getting confused between it's own internal use of @@IDENTITY and @@SCOPE_IDENTITY.<<
You understand that these can return different results, right? In other words, they should not be used interchangeably.
You understand that these can return different results, right? In other words, they should not be used interchangeably.
And trust me, this has nothing to do with SQL Server and everything to do with the interface to SQL Server.
ASKER
Yes I clearly understand the difference and I use them appropriately in my stored procedures. In this case everything is happening inside a trigger which doesn't use either of these. Since the trigger works fine when the child tables primary key is not an identity, I was just guessing as to what SQL Server might be doing differently as it talks to its user interfaces.
ASKER
I've temporarily worked around the problem by changing the identity primary key of tblAmenitySpecMeasure to type uniqueidentifier with a default value of NEWID(). Are there any issues if I change the primary keys on all my tables from identity int to non identity uniqueidentifier? What is the equivalent of @@SCOPE_IDENTITY so that an insert stored procedure can provide the new GUID to the caller as an output paramenter? In VBA, do I use adGUID as the paramenter type for the ADODB command?
>>Are there any issues if I change the primary keys on all my tables from identity int to non identity uniqueidentifier? <<
Yes a very big one. One takes up 4 bytes, the other 16.
>>What is the equivalent of @@SCOPE_IDENTITY so that an insert stored procedure can provide the new GUID to the caller as an output paramenter?<<
There isn't. You will have to do something like this:
Create Procedure usp_InsertTable1
@NewGuid uniqueidentifier OUTPUT
SET @NewGuid = NEWID()
Insert Table1 (Col1, Col2,...) Values (@NewGuid, Col2, ...)
>>In VBA, do I use adGUID as the paramenter type for the ADODB command?<<
No, there is no "GUID" variable type in VB. You use adVarChar and pass it with a length of 36 bytes.
Yes a very big one. One takes up 4 bytes, the other 16.
>>What is the equivalent of @@SCOPE_IDENTITY so that an insert stored procedure can provide the new GUID to the caller as an output paramenter?<<
There isn't. You will have to do something like this:
Create Procedure usp_InsertTable1
@NewGuid uniqueidentifier OUTPUT
SET @NewGuid = NEWID()
Insert Table1 (Col1, Col2,...) Values (@NewGuid, Col2, ...)
>>In VBA, do I use adGUID as the paramenter type for the ADODB command?<<
No, there is no "GUID" variable type in VB. You use adVarChar and pass it with a length of 36 bytes.
ASKER
Another weekend and tons of hours looking for a viable workaround finally yields a solution and some other important obseverations. I tried the following scenarios that all produced the same problem as long as I used an identity primary key in the child table:
- changing the insert trigger to an instead of insert trigger
- using a cursor in the insert trigger to do the child inserts via a stored procedure
Since the child table being inserted into by the insert trigger of the parent table is actually a many to many intersection of 2 parent tables, I removed the identity primary key and replaced it with the 2foreign key columns that point to the 2 parent tables. The minor downside is that the update trigger on the child table is slightly more complex and I can't use the primary key column to determine the order of inserts.
The other important thing I learned is that when you are adding a row via the new record on a form, and the associated insert trigger inserts rows into other tables, you must SET NOCOOUNT ON in the trigger or Access will raise the error "key column information is insufficient or incorrect; too many rows affected by update"
SUCCESS AT LAST !!!!! (But I still wonder why this specific combination of tables has a problem with identity columns and many other cases don't have this problem).
- changing the insert trigger to an instead of insert trigger
- using a cursor in the insert trigger to do the child inserts via a stored procedure
Since the child table being inserted into by the insert trigger of the parent table is actually a many to many intersection of 2 parent tables, I removed the identity primary key and replaced it with the 2foreign key columns that point to the 2 parent tables. The minor downside is that the update trigger on the child table is slightly more complex and I can't use the primary key column to determine the order of inserts.
The other important thing I learned is that when you are adding a row via the new record on a form, and the associated insert trigger inserts rows into other tables, you must SET NOCOOUNT ON in the trigger or Access will raise the error "key column information is insufficient or incorrect; too many rows affected by update"
SUCCESS AT LAST !!!!! (But I still wonder why this specific combination of tables has a problem with identity columns and many other cases don't have this problem).
rmk, As yo have self answered this could you please request PAQ + point refund. Cheers
PS Thanks for providing your solution
PS Thanks for providing your solution
I agree with the above. Here's how:
I answered my question myself. What do I do?
https://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi70
I answered my question myself. What do I do?
https://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi70
Learned, suggest PAQ + refund
"As yo have self answered this could you please request PAQ + point refund. Cheers"
"As yo have self answered this could you please request PAQ + point refund. Cheers"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.