PRB: Unable to Delete from Table Having Cascade Delete Trigger (194981)

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.

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

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 <username> and Password <strong 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=<user name>;Password=<strong 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.

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