table1
大类
A
B
table2
id 大类 备注
N1 A N1 and A
N1 B N1 and B
N2 A N2 and A
N2 B N2 and B1,我想做个触发器,当有N3插入table2的id列中,table2自动成2行如下
N3 A N1 and A
N3 B N1 and BCREATE trigger tr_tabl2insert
on table2
for insert
as
......
2,或者存储器过程也可以,当检查到已经在id列加入一行N3后,自动生成2行.
3,再或者,当检查到已经在id列加入2行N3后,在其后面的列中生成A N1 and A及B N1 and B注:A和B是从table1中查询出来的.
给出一种方法就可以,应该说第三个是相对实现起来简单点.请高手指教
大类
A
B
table2
id 大类 备注
N1 A N1 and A
N1 B N1 and B
N2 A N2 and A
N2 B N2 and B1,我想做个触发器,当有N3插入table2的id列中,table2自动成2行如下
N3 A N1 and A
N3 B N1 and BCREATE trigger tr_tabl2insert
on table2
for insert
as
......
2,或者存储器过程也可以,当检查到已经在id列加入一行N3后,自动生成2行.
3,再或者,当检查到已经在id列加入2行N3后,在其后面的列中生成A N1 and A及B N1 and B注:A和B是从table1中查询出来的.
给出一种方法就可以,应该说第三个是相对实现起来简单点.请高手指教
insert into table1 values('A')
insert into table1 values('B')
create table table2(id varchar(10) , 大类 varchar(10) , 备注 varchar(20))
insert into table2 values('N1' , 'A' , 'N1 and A')
insert into table2 values('N1' , 'B' , 'N1 and B')
insert into table2 values('N2' , 'A' , 'N2 and A')
insert into table2 values('N2' , 'B' , 'N2 and B')
gocreate procedure my_proc @id varchar(10)
as
begin
if @id = 'N3'
insert into table2 select id = @id , t.大类 , 备注 = 'N1 and ' + t.大类 from table1 t
end
goexec my_proc 'N3'select * from table2drop table table1 , table2
drop procedure my_proc/*
id 大类 备注
---------- ---------- --------------------
N1 A N1 and A
N1 B N1 and B
N2 A N2 and A
N2 B N2 and B
N3 A N1 and A
N3 B N1 and B(所影响的行数为 6 行)*/
create table table1(大类 varchar(10))
insert into table1 values('A')
insert into table1 values('B')
create table table2(id varchar(10) , 大类 varchar(10) , 备注 varchar(20))
insert into table2 values('N1' , 'A' , 'N1 and A')
insert into table2 values('N1' , 'B' , 'N1 and B')
insert into table2 values('N2' , 'A' , 'N2 and A')
insert into table2 values('N2' , 'B' , 'N2 and B')
gocreate procedure my_proc @id varchar(10)
as
begin
if @id = 'N3'
begin
delete from table2
insert into table2 select id = @id , t.大类 , 备注 = 'N1 and ' + t.大类 from table1 t
end
end
goexec my_proc 'N3'select * from table2drop table table1 , table2
drop procedure my_proc/*
id 大类 备注
---------- ---------- --------------------
N3 A N1 and A
N3 B N1 and B(所影响的行数为 2 行)*/
原来的不删除
自动生成2行为
N3 A N3 and A
N3 B N3 and B
同理类推
只要id列有insert 如N4,N5,N6
自动生成2行
N4 A N4 and A
N4 B N4 and B
N5 A N5 and A
N5 B N5 and B
N6 A N6 and A
N6 B N6 and B
...
类推
同理table1中的类别也不仅是AB大概20行,不过是固定的能通过select 大类 from table1查出的.
所以没那么简单的,谢谢高手回答.
create table table1(大类 varchar(10))
insert into table1 values('A')
insert into table1 values('B')
create table table2(id varchar(10) , 大类 varchar(10) , 备注 varchar(20))
insert into table2 values('N1' , 'A' , 'N1 and A')
insert into table2 values('N1' , 'B' , 'N1 and B')
insert into table2 values('N2' , 'A' , 'N2 and A')
insert into table2 values('N2' , 'B' , 'N2 and B')
gocreate procedure my_proc @id varchar(10)
as
begin
delete from table2 where id = @id --删除原来存在的相同的id,如果不删除,自己注销掉这句。
insert into table2 select id = @id , t.大类 , 备注 = @id + ' and ' + t.大类 from table1 t
end
goexec my_proc 'N3'
exec my_proc 'N4'
exec my_proc 'N5'select * from table2drop table table1 , table2
drop procedure my_proc/*
id 大类 备注
---------- ---------- --------------------
N1 A N1 and A
N1 B N1 and B
N2 A N2 and A
N2 B N2 and B
N3 A N3 and A
N3 B N3 and B
N4 A N4 and A
N4 B N4 and B
N5 A N5 and A
N5 B N5 and B(所影响的行数为 10 行)*/