SQL Server Update values in a view that is a Union of two tables

envsm3lx  于 2023-11-16  发布在  其他
关注(0)|答案(2)|浏览(126)

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?

yks3o0rb

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:

create table t1 (id Int constraint t1chk check (id < 10)  primary key, other columns);

See a working example Fiddle here.

mrzz3bfm

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:

SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO
CREATE TRIGGER V_View_UpD
ON dbo.V_View
INSTEAD OF UPDATE
AS

SET NOCOUNT, XACT_ABORT ON;

UPDATE T
SET UserComment = I.UserComment
FROM dbo.TABLE_A T
    JOIN inserted I
        ON T.GlobalID = I.GlobalID
    JOIN deleted D
        ON T.GlobalID = D.GlobalID
WHERE COALESCE(I.UserComment, '') <> COALESCE(D.UserComment, '');

UPDATE T
SET UserComment = I.UserComment
FROM dbo.TABLE_B T
    JOIN inserted I
        ON T.GlobalID = I.GlobalID
    JOIN deleted D
    ON T.GlobalID = D.GlobalID
WHERE COALESCE(I.UserComment, '') <> COALESCE(D.UserComment, '');
GO

相关问题