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
Solved

Trigger Does Not Fire on View When Updated Through ADO

Posted on 2004-09-10
14
266 Views
Last Modified: 2006-11-17
[I have also posted this question in the SQL section and will award points on either or both for a good solution]

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
  • 6
  • 4
  • 3
14 Comments
 
LVL 46

Accepted Solution

by:
fritz_the_blank earned 500 total points
ID: 12030102
My understanding is that you cannot update a recordset that has multiple tables--you must update each table separately.

FtB
0
 

Author Comment

by:CMcLennan
ID: 12030201
I certainly hope that's not the case :/

The microsoft knowledge base article I linked certainly seems to indicate it's possible.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12030268
Right--but notice how they update each table individually through a separate literal sql insert. You are attempting to update a recordset that may comprise multiple tables.

FtB
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

Author Comment

by:CMcLennan
ID: 12030409
Don't mean to be a pain but those separate literal sql insert statements are just populating the test data in the tables.

I don't think you're understanding the code on the knowledge base article.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12030539
Sorry, I misread this. The first part of your code is fine--it updates a single table through the recordset, so that should be fine.

Let me take a quick look at the second part. My next thought is that this may be permissions based--'80004005' is often a permissions related error, and if your trigger works fine when called through a server-side event, it seems to function fine. I wonder if the IUSR_ account needs to have permissions within SQL Server in order for this to work?

FtB
0
 

Author Comment

by:CMcLennan
ID: 12031106
Thanks FtB.  I don't believe this is a permission error.  When I change to code to connect to tblAuthor_Org directly it works fine.  This seems very much caused by the trigger not firing.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12031627
I am sorry, I was thinking about whether the IUSR account needed permissions in order for the trigger to fire.

FtB
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12031699
In any event, if this does not work on the server, then permissions are not the issue. I really hope that one of the folks in the SQL Server TA can set this straight for you.

FtB
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 12034875
See my comment in your cross posted question:
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21126652.html

As a cautionary note, EE frowns upon more than 500 points offered per question.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 12034948
Fritz,

Actually it looks like if anything the othere thread needs to be deleted.   You were right in your original assessment. See my comments from the other thread:

<quote>
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
</quote>
0
 

Author Comment

by:CMcLennan
ID: 12060261
Hi all,

Sorry didn't mean to over-spend with my cross post. I just really needed a solution.  

I guess although I can get the trigger to work on the server and update both tables through the view, I can't do the same thing from ADO with the same call.

If 'it can't be done' is the answer that's still an answer isn't it.

How would you recommend I handle the points?

Thanks,

Christy
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12060368
You may request a refund and delete in the Community Support area or you may accept the comments that helped you the most as an answer. If more than one person was helpful in a thread, please use the split button to distribute points to reflect how you feel you were helped by each contributor.

FtB
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 12060767
Once again the problem is not in the Trigger, but rather in the fact that you are attempting to update a View made up of more than table.  If the columns to be updated belong to more than one table, it cannot be done. EOS
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

792 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