请教一个出发器的问题
有两个表:
user表(userid,username,userpwd,sex,departid) //userid主键
department表(departid,departname,membercount)//departid主键
两表建立了外键约束
想用触发器实现在更新user表是自动更新department表.请问可以在一个触发器中实现吗?若不可以两个或三个可以吗?
我新手,不知道如何获取已改变记录的id,请大家不吝赐教,最好带实例,谢谢
[解决办法]
当然可以了，应该在user表上建触发器，当user表有变化时，会产生一个临时表deleted或inserted表，这表里记录了变化的记录，通过这个变化的记录deperid列可以对depart表进行操作如增加部门人数等
[解决办法]
可以一个触发器
create trigger tr_user
on user
for insert,update,delete
as
update a
set membercount=a.membercount+b.membercount
from department a,(
select departid,sum(membercount) as membercount
from (
select departid,1 as membercount
from inserted
union all
select departid,-1 as membercount
from deleted
) as t
group by departid
having sum(membercount) <> 0
) as b
where a.departid=b.departid
go
[解决办法]
我想实现的是在更新数据表user时自动更新department表时elementcount字段记录的人数和user表中的记录一致
-------------------------
user 表中沒有什么統計字段啊
[解决办法]
create trigger update_department on user
for insert,delete
as
begin
update department set membercount=(select count(1) from user where a.departid=departid)
from department a,user b
where a.departid=b.departid
end
