如何向视图插入数据
/* drop table a drop table b go */ 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_view go */ create view AB_view as select 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 insert as begin insert into a(Avalue) select Avalue from ( select distinct id,Avalue from inserted i )t insert into b(aid,Bvalue) select scope_identity(),Bvalue from inserted i end go insert into AB_view (Avalue, Bvalue) values ('A3', 'B3') select * from a /* ID Avalue 1 A3 */ select * from b /* ID AID Bvalue 1 1 B3 */
/* --建立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 t1 select object_id,'01' from sys.objects insert into t2 select OBJECT_ID ,'02' from sys.objects if exists(select * from sys.views where name = 'v_t') drop view v_t go --创建视图 create view v_t as select * from t1 union all select * from t2 go */ if exists(select * from sys.triggers where name = 'trigger_t') drop trigger dbo.trigger_t go --1.通过判断区分列tbl的值是'01'或'02',把数据分别插入t1或t2 create trigger dbo.trigger_t on dbo.v_t instead of insert as declare @t varchar(8); set @t = ''; if @t = '01' insert into dbo.t1 select * from inserted else insert into dbo.t2 select * from inserted go --1.测试 insert into v_t select 115,'02' select * from dbo.t2 where ID = 115 if exists(select * from sys.triggers where name = 'trigger_t') drop trigger dbo.trigger_t go --2.通过判断区分列tbl的值是'01'或'02',来动态生成语句实现插入数据 create trigger dbo.trigger_t on dbo.v_t instead of insert as declare @t varchar(8); declare @sql varchar(max); declare @id int ; set @t = ''; select @t = tbl,@id = ID from inserted set @sql = 'insert into dbo.t' + RIGHT(@t,1) + '(id,tbl) values(' + + CAST(@id as varchar) + ',''' + @t +''')' exec(@sql) go --2.测试 insert into v_t select 116,'01' select * from dbo.t1 where ID = 116 if exists(select * from sys.triggers where name = 'trigger_t') drop trigger dbo.trigger_t go --3.前两种是通过insted of触发器来实现的,接下来通过check约束来实现 --先删除原来的主键,因为分区列必须包含在主键中 alter table t1 drop constraint PK__t1__3213E83F7F60ED59 --可以不加check约束,但会导致查询优化器必须要搜索所有的表 --加了check约束后,会直接搜索符合分区依据列的表. alter table t1 add constraint ck_t1_tbl check(tbl='01') --加上主键约束 alter table t1 add constraint pk_t1 primary key(id,tbl) alter table t2 drop constraint PK__t2__3213E83F03317E3D alter table t2 add constraint ck_t2_tbl check(tbl='02') alter table t2 add constraint pk_t2 primary key(id,tbl) --3.测试 insert into v_t select 12345,'01' select * from dbo.t1 where ID = 12345 --4.建立索引视图 if exists(select * from sys.views where name = 'v_t') drop view v_t go --创建视图 --必须加上架构,以及明确指定列 create view dbo.v_t with schemabinding as select ID,tbl from dbo.t1 union all select id,tbl from dbo.t2 go select * 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)