Solved

Trigger Does Not Fire on View When Updated Through ADO

Posted on 2004-09-10
4
411 Views
Last Modified: 2010-05-18
[I may post this question within the ASP section as well.]

The trigger in question works perfectly in Query Analyzer, but when I call it from my ASP page through ADO (code below) the trigger isn't being fired.  I get the error:
Microsoft OLE DB Provider for SQL Server error '80004005' Cannot insert or update columns from multiple tables.

For clarity -  tblAuthor is a view which I'm trying to use to replace the real tblAuthor (renamed to tblAuthor_Org)

The code on the ASP page is striaght forward:
Set adoCon = Server.CreateObject("ADODB.Connection")
Set rsCommon = Server.CreateObject("ADODB.Recordset")
adoCon.connectionstring = strCon
adoCon.Open
strSQL = "SELECT * FROM tblAuthor where Author_ID= 5"
rsCommon.CursorType = 2
rsCommon.LockType = 3
rsCommon.Open strSQL, adoCon
With rsCommon
        strRealName = "Christy M"
        .Fields("Real_name") = strRealName
        strHomepage="www.url.com"
        .Fields("Homepage") = strHomepage
        .Update
End With

I really don't want to have to change the way the query is being called with ADO.  I'm trying to implement the joining of two databases of information by using a view to grab pieces for each.  If I can replace the table tblAuthor with a view called tblAuthor I'd be set but first I need to be able to update the view (ie- use a trigger).

View:
CREATE VIEW dbo.tblAuthor
WITH  VIEW_METADATA
AS
SELECT     auth.Author_ID, auth.Group_ID, auth.Username, auth.Real_name, auth.User_code, cia.Password, auth.Salt, email.Email AS Author_Email,
                      cia.WWW AS Homepage, auth.PM_notify, cia.CIAID
FROM         DB1.dbo.tblAuthor_Org auth INNER JOIN
                      DB2.dbo.Email email ON email.Email = auth.Author_email INNER JOIN
                      DB2.dbo.CIA cia ON email.CIAID = cia.CIAID

The simple trigger I'm using to test:
CREATE TRIGGER dbo.TestUpdateTrigger ON [dbo].[tblAuthor]
INSTEAD OF Update
AS
Update tblAuthor_Org SET Real_name=(SELECT CIAID FROM inserted) WHERE Author_ID=(SELECT Author_ID FROM inserted)

The trigger will actually be changed to DO something if I can get this to work.  As it is it would simply prove that data from the second database was being moved into the first

I found a knowledge base article on Microsofts site that I thought for sure would fix the problem http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q304096 Unfortunately it still didn't work after adding WITH  VIEW_METADATA (the MS suggested fix) to the Create View statement.

Thanks in advance for the help.  Please let me know if any additional details are required.
0
Comment
Question by:CMcLennan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 12034857
>>Cannot insert or update columns from multiple tables.<<
Change your Trigger as follows:

CREATE TRIGGER dbo.TestUpdateTrigger ON [dbo].[tblAuthor]
INSTEAD OF Update
AS
Update      tblAuthor_Org
SET            Real_name = Inserted.CIAID
From      tblAuthor_Org
            Inner Join Inserted On tblAuthor_Org.Author_ID = Inserted.Author_ID
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 12034935
Actually I may have totally misunderstood the question. The problem is that you are attempting to update a view made up of more than one table and is unrelated to the trigger.  I suspect the error is ocurring in the Update method and is not going beyod that to the Trigger.  
Simply put, in general you cannot do an update when more than one table is involved.  See here for more info:
PRB: ASP Error "The Query Is Not Updateable" When You Update Table Record
http://support.microsoft.com/default.aspx?scid=kb;en-us;174640
0
 

Author Comment

by:CMcLennan
ID: 12298443
My appologies.

I did not intend to leave this unanswered.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question