Microsoft KB Archive/194981

= PRB: Unable to Delete from Table Having Cascade Delete Trigger =

Article ID: 194981

Article Last Modified on 3/14/2005

-

APPLIES TO


 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7

-



This article was previously published under Q194981



SYMPTOMS
Deleting a record from a SQL Server table with a cascade delete trigger causes the following error message when there are no matching rows in the child table if the OLE DB provider is used for SQL Server:

-2147217864 The specified row could not be located for updating: Some values may have been changed since it was last read.



CAUSE
The only way that the Client Cursor Engine can determine whether the update succeeded is by interpretation of the return code from the provider's ICommand::Execute. If the execution returns a failure code, the update is presumed to have failed.

In the preceding case, the trigger is disguising the results of the original operation. The row count for the SECOND delete is being returned as the [pcRowsAffected] for the original delete in the Cursor Engine's call to ICommand::Execute. Since the Cursor Engine is told that zero (0) rows were affected by the DELETE call, it concludes that there was a concurrency violation and causes the error to display.



RESOLUTION
You have to explicitly add "SET NOCOUNT ON" in the trigger to prevent it from returning the "0 rows affected" message to the Sqloledb provider's cursor engine. The cursor engine interprets the "0 rows affected" to mean the delete failed.



STATUS
This behavior is by design.



Task One: Create Test Table and Trigger
For testing purposes, tables TableA and TableB will be created in the Pubs database with TableA being the parent table and TableB being the child table. Add a single row to TableA and TableB is left empty to reproduce the problem. Create a delete trigger on TableA that deletes all matching rows from TableB in order to maintain referential integrity.

For creating the tables and the trigger open ISQL/W, select the pubs database and execute the following SQL script one line at a time: /* Create the Parent Table */ Create table TableA (id int NOT NULL,name varchar(10) NOT NULL) /* Create the Child table */ Create table TableB (id int NOT NULL,name varchar(10) NOT NULL) /* Add a row to TableA */ Insert into tableA values (1,'xxx') go     /* Create trigger on TableA */ Create trigger TableA_trigger1 On TableA for delete as     delete TableB from TableB, deleted where TableB.id = deleted.id

Task Two: Build the Visual Basic Code
 Open a new Standard .exe project in Visual Basic. Form1 is created by default. Add a Command button to the Form. Command1 is added by default. From the Project menu, choose References, and select the Microsoft ActiveX Data Objects Library.  Paste the following code in the Code window.

Note You must change User ID and Password to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database. Option Explicit

Private Sub Command1_Click Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset Dim strCnnODBC As String Dim strCnnOLEDB As String On Error goto err_trans strCnnOLEDB = "Provider=SQLOLEDB.1;User ID= ;Password= " & _ "Initial Catalog=Pubs;Data Source=sujoy" cnn.Open strCnnOLEDB rst.CursorLocation = adUseClient rst.Open "select * from tableA", cnn, _ adOpenStatic,adLockBatchOptimistic rst.MoveFirst rst.Delete adAffectCurrent 'Delete the Row from the Parent Table cnn.BeginTrans rst.UpdateBatch adAffectCurrent ' Error here cnn.CommitTrans cnn.Close Exit Sub err_trans: Debug.Print Err.Number & " " &a  Err.Description cnn.RollbackTrans cnn.Close End Sub  Run the application, click Command1 and you get the error.</li>  Re-create the trigger with "SET NOCOUNT ON" to avoid the error. To do this, execute the following SQL script in ISQL/W one line at a time: Use Pubs drop trigger TableA_trigger1 go     Create trigger TableA_trigger1 On TableA for delete As     Set NOCOUNT ON      delete TableB from TableB, deleted where TableB.id = deleted.id                   </li></ol>

Keywords: kbbug kbstoredproc kbdatabase kbprb kbpending KB194981

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.