PRB: Unable to Delete from Table Having Cascade Delete Trigger

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

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