alter trigger trig_FeedbackNumber_tDetaineesInfo
on tDetaineesInfo
for insert,delete,update
as
if exists(select 1 from inserted)
begin update room
set room.iNumber=t.number
from tPrisonRoom as room join
(
select iPrisonRoomID,count(*) as number
from tDetaineesInfo
where iPrisonRoomID in(select iPrisonRoomID from inserted)
group by iPrisonRoomID
) as t
on(room.OID=t.iPrisonRoomID)
end
if exists(select 1 from deleted)
begin select *
from tDetaineesInfo
where iPrisonRoomID in(select iPrisonRoomID from deleted) select count(*)
from tDetaineesInfo
where iPrisonRoomID in(select iPrisonRoomID from deleted)
update room
set room.iNumber=t.number
from tPrisonRoom as room join
(
select iPrisonRoomID,count(*) as number
from tDetaineesInfo
where iPrisonRoomID in(select iPrisonRoomID from deleted)
group by iPrisonRoomID
) as t
on(room.OID=t.iPrisonRoomID)
end
输出的结果是:
1、多条记录内容
2、老显示1不知道怎么回事,请大侠们给予解答。
谢谢回复,可是select *
from tDetaineesInfo
where iPrisonRoomID in(select iPrisonRoomID from deleted) select count(*)
from tDetaineesInfo
where iPrisonRoomID in(select iPrisonRoomID from deleted)这两个语句的输出结果不相符啊。
谢谢回复,我就执行了:update tDetaineesInfo
set vRe='1111'
where oid=71
on tDetaineesInfo
for insert,delete,update
as
IF @@ROWCOUNT>0
begin
UPDATE a
SET iNumber=t.number
FROM tPrisonRoom AS a
INNER JOIN (SELECT iPrisonRoomID,count(*) as number FROM tDetaineesInfo AS a
WHERE EXISTS(SELECT 1 FROM DELETED WHERE iPrisonRoomID=a.iPrisonRoomID) OR EXISTS(SELECT 1 FROM INSERTED WHERE iPrisonRoomID=a.iPrisonRoomID)
GROUP BY iPrisonRoomID) AS b ON a.OID=b.iPrisonRoomID
END
这样测测
--楼主既然tDetaineesInfo表执行了
update tDetaineesInfo
set vRe='1111'
where oid=71--那
select count(*)
from tDetaineesInfo
where iPrisonRoomID in(select iPrisonRoomID from deleted)
--的结果就是聚合函数的集--而select *
from tDetaineesInfo
where iPrisonRoomID in(select iPrisonRoomID from deleted)--是所有符合条件的记录
--当然不一样啊
alter trigger trig_FeedbackNumber_tDetaineesInfo
on tDetaineesInfo
for insert,delete,update
as
IF @@ROWCOUNT>0
begin
UPDATE a
SET iNumber=b.number
FROM tPrisonRoom AS a
INNER JOIN (SELECT iPrisonRoomID,count(*) as number FROM tDetaineesInfo AS a
WHERE EXISTS(SELECT 1 FROM DELETED WHERE iPrisonRoomID=a.iPrisonRoomID) OR EXISTS(SELECT 1 FROM INSERTED WHERE iPrisonRoomID=a.iPrisonRoomID)
GROUP BY iPrisonRoomID) AS b ON a.OID=b.iPrisonRoomID
END
on tDetaineesInfo
for insert,delete,update
as
IF @@ROWCOUNT>0
begin
UPDATE a
SET iNumber=ISNULL(b.number,0)
FROM tPrisonRoom AS a
LEFT JOIN (SELECT iPrisonRoomID,count(*) as number FROM tDetaineesInfo AS a
WHERE EXISTS(SELECT 1 FROM DELETED WHERE iPrisonRoomID=a.iPrisonRoomID) OR EXISTS(SELECT 1 FROM INSERTED WHERE iPrisonRoomID=a.iPrisonRoomID)
GROUP BY iPrisonRoomID) AS b ON a.OID=b.iPrisonRoomID
WHERE EXISTS(SELECT 1 FROM DELETED WHERE iPrisonRoomID=a.iPrisonRoomID) OR EXISTS(SELECT 1 FROM INSERTED WHERE iPrisonRoomID=a.iPrisonRoomID)
END