/*
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)