34,576
社区成员
发帖
与我相关
我的任务
分享
create table dept(id int, name varchar(10), num int);
create table emp(id int, did int, name varchar(10));
go
create trigger emp_ins
on emp after insert
as
begin
with x as(select did , count(*) n from inserted group by did)
update dept set num = isnull(num,0) + x.n
from x , dept where dept.id = x.did
end
go
insert into dept values(1,'dev',0),(2,'sales',0)
go
print '插入员工数据前'
select * from dept
go
insert into emp values(1,1,'zhangsan'),(2,1,'lisi'),(3,2,'wangwu')
go
print '插入员工数据后'
select * from dept
go
drop table dept , emp
go
插入员工数据前
id name num
----------- ---------- -----------
1 dev 0
2 sales 0
插入员工数据后
id name num
----------- ---------- -----------
1 dev 2
2 sales 1