PRB: Trigger Does Not Fire on View When Updated Through ADO (304096)

MORE INFORMATION

Steps to Reproduce Behavior

1. Run the following SQL Server script code to create 2 tables, a view, and an INSERT and UPDATE INSTEAD OF trigger:

   if exists (select * from dbo.sysobjects where id = 
   object_id(N'[dbo].[MyTrigger]') and OBJECTPROPERTY(id, N'IsTrigger') = 1
   drop trigger [dbo].[MyTrigger] 
   GO
   if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MyUpdTrig]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
   drop trigger [dbo].[MyUpdTrig]
   GO 
   if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[VIEW1]') and OBJECTPROPERTY(id, N'IsView') = 1)
   drop view [dbo].[VIEW1]
   GO 
   if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
   drop table [dbo].[Table1]
   GO
   if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
   drop table [dbo].[Table2]
   GO 
   CREATE TABLE [dbo].[Table1] ( [fld1] [char] (10) COLLATE
    SQL_Latin1_General_CP1_CI_AS NULL , [fld2] [char] (10) COLLATE
    SQL_Latin1_General_CP1_CI_AS NULL , [fld3] [int] NOT NULL ) ON [PRIMARY]
    GO 
   CREATE TABLE [dbo].[Table2] ( [f1] [char] (10) COLLATE
    SQL_Latin1_General_CP1_CI_AS NULL , [f2] [char] (10) COLLATE
    SQL_Latin1_General_CP1_CI_AS NULL , [f3] [int] NOT NULL ) ON [PRIMARY]
   GO 
   ALTER TABLE [dbo].[Table1] WITH NOCHECK ADD CONSTRAINT [PK_Table1] 
   PRIMARY KEY CLUSTERED ( [fld3] ) ON [PRIMARY] 
   GO
   ALTER TABLE [dbo].[Table2] WITH NOCHECK ADD CONSTRAINT [PK_Table2]
   PRIMARY KEY CLUSTERED ( [f3] ) ON [PRIMARY] 
   GO 
   CREATE VIEW dbo.VIEW1 AS SELECT dbo.Table1.*, dbo.Table2.* 
   FROM dbo.Table1 INNER JOIN dbo.Table2 
   ON dbo.Table1.fld3 = dbo.Table2.f3
   GO
   CREATE TRIGGER [MyTrigger] ON View1 Instead Of Insert AS 
   RAISERROR (50009, 16, 10) 
   GO
   CREATE TRIGGER [MyUpdTrig] ON View1 Instead Of Update AS
   RAISERROR (50009, 16, 10)
   GO 
   					

   
   
   

   ' This code adds a new user defined message. 
   sp_addmessage 50010, 19, 'The trigger was fired' 
   
   ' This code adds some test data to the tables. 
   INSERT Table1 (fld1, fld2, fld3) VALUES ( 'aaa', 'AAA', 1 ) 
   INSERT Table1 (fld1, fld2, fld3) VALUES ( 'bbb', 'BBB', 2 ) 
   INSERT Table2 (f1, f2, f3) VALUES ( 'aaa', 'AAA', 1 ) 
   INSERT Table2 (f1, f2, f3) VALUES ( 'bbb', 'BBB', 2 ) 
   					

2. Open the view in a recordset object and attempt to update the view. To do this, follow these steps:
   1. In Microsoft Visual Basic 6.0, create a new Standard EXE project.
   2. Add a CommandButton to the form.
   3. Add a reference to the Microsoft ActiveX Data Objects 2.x Library.
   4. Paste the following code in the General Declarations section of the form:

      Dim cn As ADODB.Connection
      Dim rs As ADODB.Recordset 
      
      Private Sub Form_Load() 
      Set cn = New ADODB.Connection 
      cn.ConnectionString = "Provider=SQLOLEDB.1;" _ &
                            "Integrated Security=SSPI;" _ & 
                            "Persist Security Info=False;" _ &
                            "Initial Catalog=TestDb;" _ &
                            "Data Source=HATTERASIV" 
      cn.CursorLocation = adUseClient 
      cn.Open 
      Set rs = New ADODB.Recordset 
      rs.LockType = adLockBatchOptimistic 
      rs.CursorLocation = adUseClient 
      rs.CursorType = adOpenStatic 
      rs.Open "MyView", cn 
      End Sub 
       
      Private Sub Command1_Click() 
      On Error GoTo Bad 
      rs.ActiveConnection = Nothing 
      
      rs.MoveLast 
      rs.AddNew 
      rs.Fields("fld1").Value = "NewValue" 
      rs.Fields("fld2").Value = "NewValue" 
      rs.Fields("fld3").Value = 86 
      rs.Fields("f1").Value = "NewValue" 
      rs.Fields("f2").Value = "NewValue" 
      rs.Fields("f3").Value = 86 
      rs.ActiveConnection = cn 
      rs.UpdateBatch 
      GoTo exitend 
      Bad: 
      While Err.Number <> 0
      MsgBox Err.Number 
      MsgBox Err.Description 
      Err.Clear 
      Wend 
      exitend: 
      End Sub 
      						

   5. Save and run the form.