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

Question

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.

Answer 1

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;

Answer 2

I believe the only way to do that is to define an instead-of trigger on each view.