Trigger Does Not Fire on View When Updated Through ADO

[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)

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

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

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?

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 Recordhttp://support.microsoft.com/default.aspx?scid=kb;en-us;174640
</quote>

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.

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

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:
The Exchange of informatio…

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…

NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…