I have two simple tables (no constraints, one primary key, no triggers, etc) hosted on SQL Server that have identical schema. I have to keep the tables separate for various reasons but I want to be able to union them together in a view for oversight. I want a member of staff to be able to edit the tables via this unioned view.
The GlobalID is the primary key of each table and it is also unique across the unioned view.
CREATE VIEW V_View AS
SELECT
[GlobalID]
,[UserComment]
FROM
TABLE_A
UNION
SELECT
[GlobalID]
,[UserComment]
FROM
TABLE_B
I've tried editing the data in this view through a couple of applications (Code on Time and Microsoft Access) but I get the same error:
Update or insert of view or function V_View failed because it contains a derived or constant field
If I remove the union from the view so that only one or the other table are contributing, the edits work perfectly so I am confident that it is the UNION command that is preventing edits.
Is there any way that I can make such a view editable? Could I construct it in a different way so that the derived/constant error does not prevent editing?
2条答案
按热度按时间yks3o0rb1#
As written the view is not updateable since SQL Server does not know which rows belong in which table.
The concept you need is known as a partitioned view. To allow the view to be updatable each table requires a check constraint on its primary key defining which range of values are allowable - for example:
See a working example Fiddle here.
mrzz3bfm2#
I am not convinced attempting to update a view like this is a good idea but an INSTEAD OF trigger should work. Something like: