--> --> (Roy)生成測試數據
 
if not object_id('A') is null
    drop table A
Go
Create table A([id] int identity,[userid] int,[blogCount] int)
Insert A
select 100,0 union all
select 101,0 union all
select 102,0
Go
--> --> (Roy)生成測試數據
 
if not object_id('B') is null
    drop table B
Go
Create table B([id] int,[userid] int,[Title] nvarchar(3))
go
Create trigger tr_B_insert on B 
for insert
as
set nocount on ;
begin
update A
set [blogCount]=a.[blogCount]+b.con
from(select [userid],count(ID) as con from inserted group by [userid]) as B
inner join A on b.[userid]=a.[userid]

insert A
select b.*
from(select [userid],count(ID) as con from inserted group by [userid]) as B
left join A on b.[userid]=a.[userid]
where a.[userid] is null
end
go

--测试
Insert B
select 1,100,N'日志1' union all
select 2,102,N'日志1' union all
select 3,100,N'日志1' union all
select 4,101,N'日志1' union all
select 5,101,N'日志1' union all
select 6,102,N'日志1' union all
select 7,102,N'日志1' union all
select 8,101,N'日志1' union all
select 9,102,N'日志1'
Go
Select * from A
/*
id    userid    blogCount
1    100    2
2    101    3
3    102    4
*/

------解决方案--------------------
SQL code
update t set blogCount=(select count(*) from 表B where userid=t.userid) from 表A t