PRB: Unable to Delete from Table Having Cascade Delete Trigger

• ActiveX Data Objects (ADO), versions 2.0, 2.1 SP2

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 the Client Cursor Engine has to 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.

MORE INFORMATION

Steps to Reproduce Behavior

Task One: Create Test Table and Trigger

      /* 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

1. Open a new Standard .exe project in Visual Basic . Form1 is created by default.



2. Add a Command button to the Form. Command1 is added by default.



3. From the Project menu, choose References, and select the Microsoft ActiveX data objects Library.



4. Paste the following code in the Code window:




      
      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=sa;" &  _
      "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 

5. Run the application, click Command1 and you get the error.



6. 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. 

Additional query words:

Keywords : kbADO200 kbADO200bug kbDatabase kbOLEDB kbSQLServ kbStoredProc kbGrpVBDB kbGrpMDAC kbDSupport kbADO210sp2 kbADO210sp2bug
Version : WINDOWS:2.0,2.1 SP2
Platform : WINDOWS
Issue type : kbprb