Microsoft KB Archive/304096

= PRB: Trigger Does Not Fire on View When Updated Through ADO =

Article ID: 304096

Article Last Modified on 10/17/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft ActiveX Data Objects 2.6

-



This article was previously published under Q304096



SYMPTOMS
When you use an INSTEAD OF trigger on a SQL Server updatable view, the trigger fires when an UPDATE, INSERT or DELETE statement occurs through the view. However, if ADO Update or UpdateBatch methods are used with a client-side cursor, using either the SQLOLEDB or the ODBC SQL Server driver, the INSTEAD OF trigger does not fire.



CAUSE
By default, ADO uses the base table names that are contained in the view to update the information in the tables. Because the trigger is associated with the view name, the trigger is not fired.



RESOLUTION
SQL Server 2000 allows you to use the VIEW_METADATA attribute when you create the updatable view. This option allows SQL Server to pass the view name instead of the base table names that are to be used during updates. Views that are created with this attribute allow triggers that are associated with the view to fire when the triggers are updated through ADO.



STATUS
This behavior is by design.



Steps to Reproduce Behavior
The following code demonstrates that the view that is created without the VIEW_METADATA attribute does not fire the trigger, and the user defined error message is not displayed.   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 )  Open the view in a recordset object and attempt to update the view. To do this, follow these steps:  In Microsoft Visual Basic 6.0, create a new Standard EXE project. Add a CommandButton to the form. Add a reference to the Microsoft ActiveX Data Objects 2.x Library.</li>  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 = &quot;Provider=SQLOLEDB.1;&quot; _ & &quot;Integrated Security=SSPI;&quot; _ & &quot;Persist Security Info=False;&quot; _ & &quot;Initial Catalog=TestDb;&quot; _ & &quot;Data Source=HATTERASIV&quot; cn.CursorLocation = adUseClient cn.Open Set rs = New ADODB.Recordset rs.LockType = adLockBatchOptimistic rs.CursorLocation = adUseClient rs.CursorType = adOpenStatic rs.Open &quot;MyView&quot;, cn End Sub Private Sub Command1_Click On Error GoTo Bad rs.ActiveConnection = Nothing

rs.MoveLast rs.AddNew rs.Fields(&quot;fld1&quot;).Value = &quot;NewValue&quot; rs.Fields(&quot;fld2&quot;).Value = &quot;NewValue&quot; rs.Fields(&quot;fld3&quot;).Value = 86 rs.Fields(&quot;f1&quot;).Value = &quot;NewValue&quot; rs.Fields(&quot;f2&quot;).Value = &quot;NewValue&quot; rs.Fields(&quot;f3&quot;).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 </li> Save and run the form.</li></ol> </li></ol>

Keywords: kbprb KB304096

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.