How do I insert a default value as part of a SQL Server View definition?

sql-server, sql-server-2008, sql-server-2008-r2

Solution

Create an instead of trigger on the view

SQL fiddle

sample tables / view

create table tblTest( id int identity, c1 varchar(10), groupid int );

create view vwtest as select c1, COALESCE(GroupId,3) GroupId 
from tblTest
where GroupID = 3;

create a trigger

CREATE TRIGGER InsteadTrigger on vwTest
INSTEAD OF INSERT
AS
BEGIN
  INSERT INTO tblTest (c1, GroupID)
       SELECT c1, 3
       FROM inserted
END;

put some test data into table / view

insert into tblTest( c1, groupid ) select 'row 1' ,3 ;
insert into tblTest( c1, groupid ) select 'row 2' ,1 ;
insert into vwTest(c1) select 'row 3';

see what we get

select * from tblTest;
select * from vwTest;

Problem

I have a number of different views which use the same underlying table, but each uses a different default value for the FK. So for my attempted code: ``` ALTER VIEW [dbo].[vwTest] AS SELECT Id, C1 AS |TestCol, COALESCE(GroupId,3) AS GroupId FROM dbo.tblTest WHERE (GroupId = 3) ``` I know "3" is hardcoded, but will be replaced. This is the line I believe is causing the hassle: ``` COALESCE(GroupId,3) AS GroupId ``` I need "3" to be inserted into tblTest.GroupId each time I insert a record into vwTest. Thanks.

Original source