表A有col1,col2,col3字段,表B有col1(唯一),CountCol1字段当向A表中插入数据后,自动向B表中插入Col1的值。例如向表A插入(‘t001’,‘w’,1)(‘t001’,‘w’,2)(‘t002’,‘w’,1)时
表B中应该有 t001,Null
t002,Null
如果可以的话,能不能同时将相同Col1字段的个数也写入B表中呢?
B表的结果应该是
t001,2
t002,1
表B中应该有 t001,Null
t002,Null
如果可以的话,能不能同时将相同Col1字段的个数也写入B表中呢?
B表的结果应该是
t001,2
t002,1
on tb1
for insert
as
begin
insert into tb2
select col1,count(*)
from inserted
group by col1
end
on tb1
after insert
as
begin
delete tb2 from tb2 inner join inserted I on tb2.col1=I.col1
insert into tb2(col1,countcole) values
select col1,n=count(*)
from tb1 inner join inserted i on tb1.col1=I.col1
group by tb1.col1
end
不能删除B表的记录,如果删除B表的记录,会相应删除A表的内容。如:删除B表中的t001,Null 记录的话,A表中所有字段为t001的记录都将被删除。
on A
for insert
as
begin
insert into B
select col1,count(*)
from inserted
group by col1
end
on tb1
after insert
as
begin
---临时表先小记一下
declare @tbSum (col1 varchar(25),num int)
insert into @tbSum(col1,num)
select col1,count(*)
from inserted
group by col1
--已存在的更新数量
update A
set A.Countcol1 = A.Countcol1 + B.num
from t2 A inner join @tbSum B on A.col1 = b.col1
where A.Col1 = B.Col1
--不存在的插入新的
insert into t2(col1,count)
select Col1,Num
from @tbSum A where not exists(select 1 from @tb2 B where A.Col1 = B.Col1 )
end
on tb1
for insert
as
begin
insert into tb2
select col1,count(*)
from inserted
group by col1
end 好像是这样
delete from C
insert into C select col1,count(*) as CountCol1 from inserted group by col1update B set B.CountCol1 =B.CountCol1 +C.CountCol1
from B inner join C on B.col1= C.col1
where B.col1= C.col1insert into B(col1,CountCol1 )
select col1,CountCol1
from C where not exists(select 1 from B where B.col1= C.col1)delete from tempTable
只是不知道这样的方法会不会使得系统开销很大,当数据多时,会不会很慢呢?
on tb1
after insert
as
begin
---临时表先小记一下
declare @tbSum (col1 varchar(25),num int)
insert into @tbSum(col1,num)
select col1,count(*)
from inserted
group by col1
--已存在的更新数量
update A
set A.Countcol1 = A.Countcol1 + B.num
from t2 A inner join @tbSum B on A.col1 = b.col1
where A.Col1 = B.Col1
--不存在的插入新的
insert into t2(col1,count)
select Col1,Num
from @tbSum A where not exists(select 1 from @tb2 B where A.Col1 = B.Col1 )
end
as
begin
declare @i int,@col1 varchar(50);
select @col1=col1 from inserted
select @i=count(*) from b where coll=@col1
if @i>0
update b set num=num+1 where coll=@col1
else if @i=0
insert into b(coll,num)
values(@col1,1)
end