• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 223
  • Last Modified:

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, 0 AS Flg
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 !!!!!
0
rmk
Asked:
rmk
  • 10
  • 7
  • 7
  • +2
1 Solution
 
ShogunWadeCommented:
"cannot delete rows with unknown keys"     You need to refresh the grid and the id will show and this problem will go away
0
 
SoftEng007Commented:
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?
0
 
rmkAuthor Commented:
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.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
ShogunWadeCommented:
"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!
0
 
rmkAuthor Commented:
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.
0
 
ShogunWadeCommented:
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.
0
 
rmkAuthor Commented:
I beg to differ. I do lots in inserts in Access forms and never have this problem.
0
 
Anthony PerkinsCommented:
>>I do lots in inserts in Access forms and never have this problem.<<
They have Triggers in MS Access?  
0
 
Anthony PerkinsCommented:
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
0
 
rmkAuthor Commented:
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.
0
 
Anthony PerkinsCommented:
>>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 ...
0
 
rmkAuthor Commented:
Short of tons and tons of work to convert to unbound forms, I'm still totally at a loss for finding a workaround.
0
 
ShogunWadeCommented:
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 ?
0
 
rmkAuthor Commented:
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.
0
 
ShogunWadeCommented:
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  ?  
0
 
rmkAuthor Commented:
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.
0
 
Anthony PerkinsCommented:
>>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.
0
 
Anthony PerkinsCommented:
And trust me, this has nothing to do with SQL Server and everything to do with the interface to SQL Server.
0
 
rmkAuthor Commented:
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.
0
 
rmkAuthor Commented:
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?
0
 
Anthony PerkinsCommented:
>>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.
0
 
rmkAuthor Commented:
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).
0
 
ShogunWadeCommented:
rmk,   As yo have self answered this could you please request PAQ + point refund.  Cheers

PS Thanks for providing your solution
0
 
Anthony PerkinsCommented:
I agree with the above.  Here's how:
I answered my question myself. What do I do?
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi70
0
 
ShogunWadeCommented:
Learned, suggest PAQ + refund

"As yo have self answered this could you please request PAQ + point refund.  Cheers"
0
 
moduloCommented:
PAQed with points refunded (500)

modulo
Community Support Moderator
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 10
  • 7
  • 7
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now