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