Instead of Trigger on View using Merge doesn't apply table default values

Question

I have an updateable view using an instead of trigger for insert/update. That trigger uses Merge. I'm finding that the Merge statement isn't applying the default constraints from the underlying physical table, although the merge documentation suggests it should.

The following example demonstrates:

create table tblTest
(
    id uniqueidentifier not null primary key default newid(),
    forename varchar(20),
    surname varchar(20) not null default 'xxyyzz'
)
go

create view vwTest as select * from tblTest
go

create Trigger vwTest_trigger_insteadof_insert_update On vwTest
Instead of Insert, Update As
begin
set nocount on
Merge tblTest t

Using
    inserted i On (t.id = i.id)

When Matched Then
    Update
        Set
        t.forename = i.forename,
        t.surname = i.surname

When Not Matched By Target Then
    Insert
    (
        id,
        forename,
        surname

    )
    Values
    (
        i.id,
        i.forename,
        i.surname

    )
OUTPUT $action, Inserted.*, Deleted.*
;
end
go

--Inserts to physical table work as expected
insert into tblTest (id) values (newid())
insert into tblTest (surname) values ('smith')

--Inserts into updateable view fail as no defaults are set
--from the underlying physical table
insert into vwTest (id) values (newid())
insert into vwTest (surname) values ('jones')

I see someone had something similar in Using default values in an INSTEAD OF INSERT trigger and solved it by copying the rows in inserted into a temporary and then altering the temp table to add in the default constraints from the physical table. I'm not sure I could tolerate the performance issues of these additional steps.

I changed my trigger code to just handle insert using an "Insert into tblTest Select * from Inserted" - same issue. I'm beginning to think I'm missing something fundamental here. — winnt93, Oct 29 '12 at 09:06

score 1 · Answer 1 · answered Mar 17 '13 at 03:53

Simple enough. In order for a default value to be used you either have to use the DEFAULT keyword, or not include it in your insert. Even a NULL value counts. In this case you are specifying the value in the insert in your trigger. If you were to change that part of it from

When Not Matched By Target Then
Insert
(
    id,
    forename,
    surname

)
Values
(
    i.id,
    i.forename,
    i.surname

)

to

When Not Matched By Target Then
Insert
(
    id,
    forename,
    surname

)
Values
(
    i.id,
    i.forename,
    DEFAULT

)

You would see the default value for surname start to appear. Unfortunately that doesn't really help you much with what you are trying to do. The best solution I can think of (and it's not great) is to put your defaults into the trigger using isnulls.

When Not Matched By Target Then
Insert
(
    id,
    forename,
    surname

)
Values
(
    ISNULL(i.id,newid()),
    i.forename,
    ISNULL(i.surname,'xxyyzz')

)

I realize that isn't great from a maintenance point of view but it will work. I did a post on the DEFAULT keyword here with a fair amount of detail if you are interested.

Instead of Trigger on View using Merge doesn't apply table default values

1 Answers1