PRB: Unable to Delete from Table Having Cascade Delete Trigger

ID: Q194981

The information in this article applies to:

ActiveX Data Objects (ADO), version 2.0

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

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

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 2.0 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 & "  " & 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 kbOLEDB kbSQLServ 
Version           : WINDOWS:2.0
Platform          : WINDOWS
Issue type        : kbprb
Solution Type     : kbpending

Last Reviewed: November 10, 1998