How to update a view that updates base tables ?

[Ranvier]

I am trying to Update a View (vwSPOpp3) which references another view which references other base tables. The update statement I am using is below and uses a join from my view to another table.

This is the code i'm using
///////
UPDATE vwSPOpp3
SET
New_ProjectManagement = tblImportOpportunities.[Proj Man],
New_SupportContract = tblImportOpportunities.[Supp Contract],
New_Labour = tblImportOpportunities.Labour,
New_Parts = tblImportOpportunities.Parts,
New_3rdParty = tblImportOpportunities.[3rd Party],
EstimatedValue = tblImportOpportunities.[Revenue (in CRM)]

FROM
vwSPOpp3
JOIN tblImportOpportunities ON
vwSPOpp3.OwnerIdName = tblImportOpportunities.OwnerIdName
AND vwSPOpp3.CustomerIdName = tblImportOpportunities.CustomerIdName
AND vwSPOpp3.Name = tblImportOpportunities.Name

///////

The error message I get tells me I can't do an update as it references base tables:

"View or function 'vwSPOpp3' is not updatable because the modification affects multiple base tables."

Can someone confirm that I can use a Trigger to get around this problem and what I should put in the trigger. Is it an INSTEAD OF UPDATE trigger? and what code should I put in the trigger so my SQL statement will work.

Any help will be really appreciated.

Thanks!

 

[nomadicalloy]

Please look at this article for some help to your problem

http://msdn.microsoft.com/msdnmag/issues/04/01/DataPoints/

http://www.xpherion.com/

 

[Ranvier]

Thanks, I have read the article and I have created an Instead of Update trigger on my VIEW but I am still having a problem (Error message: View 'vwSPOpp3' has an INSTEAD OF UPDATE trigger and cannot be a target of an UPDATE FROM statement.)

I have created the following View:

//////
CREATE VIEW dbo.vwSPOpp3
AS
SELECT OwnerId, OwnerIdName, CreatedOn, CustomerIdName, Name, New_ProjectManagement, New_SupportContract, New_Labour, New_Parts,
New_3rdParty, EstimatedValue
FROM dbpportunity
WHERE (CreatedOn > CONVERT(DATETIME, '2007-01-01 00:00:00', 102)) AND (CreatedOn < CONVERT(DATETIME, '2007-03-26 00:00:00', 102))
//////

My Update trigger on the view is as follows:

///////
CREATE TRIGGER TrUpdatevwSPOpp3 ON vwSPOpp3
INSTEAD OF UPDATE
AS

UPDATE vwSPOpp3
SET
New_ProjectManagement = tblImportOpportunities.[Proj Man],
New_SupportContract = tblImportOpportunities.[Supp Contract],
New_Labour = tblImportOpportunities.Labour,
New_Parts = tblImportOpportunities.Parts,
New_3rdParty = tblImportOpportunities.[3rd Party],
EstimatedValue = tblImportOpportunities.[Revenue (in CRM)]

FROM
vwSPOpp3
INNER JOIN tblImportOpportunities ON
vwSPOpp3.OwnerIdName = tblImportOpportunities.OwnerIdName
--AND vwSPOpp3.CreatedOn = tblImportOpportunities.CreatedOn
AND vwSPOpp3.CustomerIdName = tblImportOpportunities.CustomerIdName
AND vwSPOpp3.Name = tblImportOpportunities.Name

GO
//////

But when i run my update statement as follows:

////////////
UPDATE vwSPOpp3
SET
New_ProjectManagement = tblImportOpportunities.[Proj Man],
New_SupportContract = tblImportOpportunities.[Supp Contract],
New_Labour = tblImportOpportunities.Labour,
New_Parts = tblImportOpportunities.Parts,
New_3rdParty = tblImportOpportunities.[3rd Party],
EstimatedValue = tblImportOpportunities.[Revenue (in CRM)]

FROM
vwSPOpp3
INNER JOIN tblImportOpportunities ON
vwSPOpp3.OwnerIdName = tblImportOpportunities.OwnerIdName
--AND vwSPOpp3.CreatedOn = tblImportOpportunities.CreatedOn
AND vwSPOpp3.CustomerIdName = tblImportOpportunities.CustomerIdName
AND vwSPOpp3.Name = tblImportOpportunities.Name
//////////////

I get the following error


/////////
View 'vwSPOpp3' has an INSTEAD OF UPDATE trigger and cannot be a target of an UPDATE FROM statement.
/////////

I thought by adding an update trigger to the view would actually perform the update I wanted. Can you tell me where I might be going wrong please?

Thanks