27,579
社区成员
发帖
与我相关
我的任务
分享
create table a(
id int identity primary key, -- 自增列,自动生成 id
to_b int, -- 该列连同 id 分拆到 b 表
to_c int -- 该列连同 id 分拆到 c 表
);
create table b(
a_id int primary key,
b int
);
create table c(
a_id int primary key,
c int
);
go
-- 自动拆分 的触发器
create trigger tr_insert on a after insert
as
insert b(a_id, b) select id, to_b from inserted;
insert c(a_id, c) select id, to_c from inserted;
go
-- 测试
insert a( to_b, to_c ) values(11,22);
insert a( to_b, to_c ) values(111,221), (333, 444);
select * from a;
select * from b;
select * from c;
go
drop table a,b,c
if object_id('ta')is not null drop table ta
create table ta(id int identity(1000,1),vb AS CONVERT(NVARCHAR(100),GETDATE(),112)+RTRIM(id),vc int)
INSERT INTO ta(vc)VALUES(1)
SELECT * FROM ta
if object_id('ta')is not null drop table ta
if object_id('tb')is not null drop table tb
if object_id('tc')is not null drop table tc
create table ta(id int identity(1000,1),vb int,vc int)
create table tb(id int ,vb int)
create table tc(id int ,vc int)
declare @t table(id int,vb int,vc int)
insert ta(vb,vc) output inserted.* into @t select 10,20 union all select 20,50
insert tb select id,vb from @t
insert tc select id,vc from @t
select * from ta
select * from tb
select * from tc
/* ta
id vb vc
1000 10 20
1001 20 50
tb
id vb
1000 10
1001 20
tc
id vc
1000 20
1001 50