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?