[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
Set rsCommon = Server.CreateObject("ADODB
adoCon.connectionstring = strCon
strSQL = "SELECT * FROM tblAuthor where Author_ID= 5"
rsCommon.CursorType = 2
rsCommon.LockType = 3
rsCommon.Open strSQL, adoCon
strRealName = "Christy M"
.Fields("Real_name") = strRealName
.Fields("Homepage") = strHomepage
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).
CREATE VIEW dbo.tblAuthor
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
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.