Sql-server – Insert into multi table View in SQL SERVER

sql serverview

I'm asking this question in manner to try to understand a similar situation I have, and to try to understand how insert to view is working.

I have these table: 

Create table A([id] int primary key not null, value nvarchar(50) NULL)
Create table B([id] int primary key not null, value nvarchar(50) NULL)

And I create view from the two tables like this:

create View V as (select * from A) UNION ALL (select * from B)

And I have this trigger on the V view:

Create trigger v_trig on V instead of insert AS
  insert into v (id,value) select id,value from inserted

When I try to insert to my view I'm getting the following error:

Msg 4436, Level 16, State 12, Procedure v_trig, Line 2

UNION ALL view 'db.dbo.V' is not updatable because a partitioning
column was not found

I have a DB with a similar view (with union all) and for some reason I can insert into it without any problem and I'm trying to understand why.

What should I do in manner to allow insert into such as view? Is there's a way to decide (without changing the trigger) which is the default table of the view in manner to insert into it?

Best Answer

You can only do this (the trigger is irrelevant) when inserting in a partitioned view, which is view that can UNION ALL data in which the underlying tables have proper CHECK constraints that are disjoint together with a primary key, so an INSERT would unmistakably go in one underlying table.

Following your example, if you restrict some values for table A, others for table B and add a compound primary key:

Create table A(
    [id] int, 
    value nvarchar(50), 
    CHECK (value = 'A'),
    PRIMARY KEY (ID, value))

Create table B(
    [id] int, 
    value nvarchar(50), 
    CHECK (value = 'B'),
    PRIMARY KEY (ID, value))

The view stays the same:

create View V as (select id, value from A) UNION ALL (select id, value from B)

And now you can successfuly insert directly into the view (no trigger required):

insert into v (id, value) select 1,'B' 
-- (1 row(s) affected)

Is there's a way to decide (without changing the trigger) which is the default table of the view in manner to insert into it?

Inserting with value = 'B' matches the CHECK for table B (the partitioned column), so the row goes that way automatically. Since the checked column has to be part of the primary key, the SQL engine knows that this row belongs in this table and not in any other one, since they all have primary keys with the same column and different check values. You can't control this manually.

If you try to insert a value that's not supported in any CHECK constraint...

insert into v (id, value) select 1,'C' 
--Msg 4457, Level 16, State 1, Line 1
--The attempted insert or update of the partitioned view failed because the value of the partitioning column does not belong to any of the partitions.
--The statement has been terminated.

Check the DDL of the tables in which the insert of your view works and you will see the disjoint CHECK constraints.

Related Question