向视图插入数据mysql_如何向视图插入数据

/*

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

/*

IDAvalue

1A3

*/

select * from b

/*

IDAIDBvalue

11B3

*/

/*

--建立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)