如何向视图插入数据

/*drop table adrop table bgo*/create table A(ID int identity(1,1) primary key,Avalue varchar(10))create table B(ID int identity(1,1) primary key,AID int,Bvalue varchar(10))go/*drop view AB_viewgo*/create view AB_viewasselect A.*, B.Bvalue from A join B on (A.ID = B.AID)go --drop trigger  AB_tr on AB_view create trigger AB_tr on AB_view instead of insertasbegininsert into a(Avalue)select Avaluefrom (select distinct id,Avaluefrom inserted i)tinsert into b(aid,Bvalue)select  scope_identity(),Bvaluefrom inserted iendgo insert into AB_view (Avalue, Bvalue) values ('A3', 'B3')select * from a/*IDAvalue1A3*/select * from b/*IDAIDBvalue11B3*/

/*--建立2个表create table t1(id int not null primary key,tbl varchar(5) not null)create table t2(id int not null primary key,tbl varchar(5) not null)go--插入数据insert into t1select object_id,'01'from sys.objectsinsert into t2select OBJECT_ID ,'02'from sys.objectsif exists(select * from sys.views where name = 'v_t')   drop view v_tgo--创建视图create view v_tasselect * from t1union allselect * from t2go*/if exists(select * from sys.triggers where name = 'trigger_t')   drop trigger dbo.trigger_tgo--1.通过判断区分列tbl的值是'01'或'02'，把数据分别插入t1或t2   create trigger dbo.trigger_ton dbo.v_tinstead of insertasdeclare @t varchar(8);set @t = '';if @t = '01'insert into dbo.t1select * from insertedelse     insert into dbo.t2    select * from insertedgo--1.测试insert into v_tselect 115,'02'select *from dbo.t2where ID = 115if exists(select * from sys.triggers where name = 'trigger_t')   drop trigger dbo.trigger_tgo--2.通过判断区分列tbl的值是'01'或'02'，来动态生成语句实现插入数据 create trigger dbo.trigger_ton dbo.v_tinstead of insertasdeclare @t varchar(8);declare @sql varchar(max);declare @id int ;set @t = '';select @t = tbl,@id = IDfrom insertedset @sql = 'insert into dbo.t' + RIGHT(@t,1) +            '(id,tbl) values(' +           + CAST(@id as varchar) + ',''' + @t +''')' exec(@sql)go--2.测试insert into v_tselect 116,'01'select *from dbo.t1where ID = 116if exists(select * from sys.triggers where name = 'trigger_t')   drop trigger dbo.trigger_tgo--3.前两种是通过insted of触发器来实现的,接下来通过check约束来实现--先删除原来的主键,因为分区列必须包含在主键中alter table t1 drop constraint PK__t1__3213E83F7F60ED59--可以不加check约束,但会导致查询优化器必须要搜索所有的表--加了check约束后,会直接搜索符合分区依据列的表.alter table t1add constraint ck_t1_tbl check(tbl='01')--加上主键约束alter table t1add constraint pk_t1 primary key(id,tbl)alter table t2drop constraint PK__t2__3213E83F03317E3Dalter table t2add constraint ck_t2_tbl check(tbl='02')alter table t2add constraint pk_t2 primary key(id,tbl)--3.测试insert into v_tselect 12345,'01'select *from dbo.t1where ID = 12345--4.建立索引视图if exists(select * from sys.views where name = 'v_t')   drop view v_tgo--创建视图--必须加上架构，以及明确指定列create view dbo.v_twith schemabindingasselect ID,tbl from dbo.t1union allselect id,tbl from dbo.t2goselect * from v_t/*报错：消息 10116，级别 16，状态 1，第 1 行无法对视图 'WC.dbo.v_t' 创建 索引，因为其中包含一个或多个 UNION、INTERSECT 或 EXCEPT 运算符。如果将查询作为原始视图的 UNION、INTERSECT 或 EXCEPT 运算符的输入，请考虑为每个这样的查询创建一个单独的索引视图。说明当视图中有union all时，无法建立索引视图*/create unique clustered index idx_v_t on dbo.v_t(id)