27,579
社区成员
发帖
与我相关
我的任务
分享
CREATE trigger str_b on A
for insert,update,DELETE
as
begin
UPDATE b
SET jhzx_pllq=n
FROM b left JOIN (select nf,count(*) as n from a GROUP BY nf) aa ON b.nf=aa.nf
end
CREATE TRIGGER tg_A ON A表
FOR DELETE,UPDATE,INSERT
AS
BEGIN
;WITH cte AS (
SELECT nf,COUNT(lqzy) AS cnt FROM A表 GROUP BY nf
)
--SELECT * FROM cte
UPDATE a SET b.Jhzx_pllq=b.cnt
FROM B表 a
inner join cte b ON a.nf=b.nf
END
上面那样试试,用你的表名替换里面的AB表[/quote]
如果重复的不算或者null也不算那就改一下成下面这样
;WITH cte AS (
SELECT nf,COUNT(distinct lqzy) AS cnt FROM A表
where isnull(lqzy,'')<>''
GROUP BY nf
)
CREATE TRIGGER tg_A ON A表
FOR DELETE,UPDATE,INSERT
AS
BEGIN
;WITH cte AS (
SELECT nf,COUNT(lqzy) AS cnt FROM A表 GROUP BY nf
)
--SELECT * FROM cte
UPDATE a SET b.Jhzx_pllq=b.cnt
FROM B表 a
inner join cte b ON a.nf=b.nf
END
上面那样试试,用你的表名替换里面的AB表