一个小功能实现，通过触发器

学生表和班级表

实现一个班级管理的转班操作

不想在程序中进行判断监控

所以直接在学生表中添加了一个触发器

通过这个触发器来修改班级表中的一些统计信息

班级表：对应班级名称、总人数、男生数、女生数

学生表：班级id，性别，姓名

因为这里只是一个测试表

触发器如下

go
alter trigger [dbo].[trigger_count]
on test_students
after INSERT,UPDATE,DELETE
as
begin 
--申明变量 用来保存接收
declare @Csum int;
declare @Msex int;
declare @Gsex int;
--如果进行的是新增操作
IF exists(select 1 from inserted) and not exists(select 1 from deleted)
begin
	select @Csum=COUNT(*),@Msex=sum(case when sex=0 then 1 else 0 end),@Gsex=sum(case when sex=1 then 1 else 0 end)  from test_students where classid in(select  classid from inserted)
	update test_calss 
	set Csum=@Csum,Msex=@Msex,GSex=@Gsex
	where ID in (select  classid from inserted);
end
--删除操作
else IF(not exists(select 1 from inserted) and exists(select 1 from deleted))
begin
	select @Csum=COUNT(*),@Msex=sum(case when sex=0 then 1 else 0 end),@Gsex=sum(case when sex=1 then 1 else 0 end)  from test_students where classid in(select  classid from deleted)
	update test_calss 
	set Csum=@Csum,Msex=@Msex,GSex=@Gsex
	where ID in (select  classid from deleted);
end
--修改操作
else IF (exists(select 1 from inserted) and exists(select 1 from deleted))
begin
select @Csum=COUNT(*),@Msex=sum(case when sex=0 then 1 else 0 end),@Gsex=sum(case when sex=1 then 1 else 0 end)  from test_students where classid in(select  classid from deleted)
	update test_calss 
	set Csum=@Csum,Msex=@Msex,GSex=@Gsex
	where ID in (select  classid from deleted);
select @Csum=COUNT(*),@Msex=sum(case when sex=0 then 1 else 0 end),@Gsex=sum(case when sex=1 then 1 else 0 end)  from test_students where classid in(select  classid from inserted)
	update test_calss 
	set Csum=@Csum,Msex=@Msex,GSex=@Gsex
	where ID in (select  classid from inserted);
end

end

测试

目前学生表数据