触发器的问题

[hxshxs]

我有两张表,其中selectCouse是一个多外键表,
selectCouse（..........,banjiid）,bj(bjid,cmax)
当表selectCouse的记录发生变化时，对selectCouse分组统计，然后把统计结果写入表B的cmax
我是这样写的触发器，我知道在trigger里面不用引用自己的表名，总是报错。


CREATE TRIGGER udi_selectCouse
ON selectCouse
FOR INSERT,UPDATE,DELETE
AS

update bj set cnum=aa.num from
(select count(*) num,banjiid from selectcourse group by banjiid) aa,bj bb where aa.banjiid=bb.bjid

小弟用sqlserver不是很多，请各路高手指点，给个正确的触发器的写法，不胜感谢

[tianhuo_soft]

up

[anovice]

update bj set cnum=aa.num from
(select count(*) num,banjiid from selectcourse group by banjiid) aa,bj bb where aa.banjiid=bb.bjid

这句运行没有错吗？？

[anovice]

只有当 select count(*) num,banjiid from selectcourse group by banjiid 只有一个值时，才不报错

[cxmcxm]

加上查询条件，只查询添加，修改与删除的记录

CREATE TRIGGER udi_selectCouse 
ON selectCouse 
FOR INSERT,UPDATE,DELETE 
AS 


update bb set cmax=aa.num
from (select count(*) num,banjiid from selectcource where banjiid in (select banjiid from inserted)
		or banjiid in (select banjiid from deleted)
	   group by banjiid) aa,bj bb where aa.banjiid=bb.bjid
--只删除记录时并全部删除掉时就删除bj表的相应记录
if not exists(select * from inserted) and not exists(select * from selectcouse where banjiid in (select banjiid from deleted))
  delete bj whree bjid in (select banjiid from deleted)

[linco001]

除了你上头写了一个cmax,后来写成cnum 别的没见有什么错误。

楼上的考虑的比较周到。解决了数据冗余问题！
但是好像写的不对。当执行insert时，deleted集合肯定为空。所以：
if not exists(select * from inserted) and not exists(select * from selectcouse where banjiid in (select banjiid from deleted))
始终为true!

只要
CREATE TRIGGER udi_selectCouse
ON selectCouse
FOR INSERT,UPDATE,DELETE
AS
begin

update bj set cmax=aa.num
from (select count(*) num,banjiid from selectcource where banjiid in (select banjiid from inserted)
or banjiid in (select banjiid from deleted)
group by banjiid) aa,bj bb where aa.banjiid=bb.bjid
--只删除记录时并全部删除掉时就删除bj表的相应记录
delete bj whree bjid in (select banjiid from deleted) and cmax = 0

end

[linco001]

CREATE TRIGGER udi_selectCouse 
ON selectCouse 
FOR INSERT,UPDATE,DELETE 
AS 
begin 

update bj set cmax=aa.num 
from (select count(*) num,banjiid from selectcource where banjiid in (select banjiid from inserted) 
        or banjiid in (select banjiid from deleted) 
      group by banjiid) aa,bj bb where aa.banjiid=bb.bjid 
--只删除记录时并全部删除掉时就删除bj表的相应记录 
  delete bj whree bjid in (select banjiid from deleted) and cmax = 0 

end