SQLServer 新增数据时触发器自动计算并更新相关值

USE [Jiao]
GO
/****** Object: Trigger [dbo].[tri_ds_insert] Script Date: 01/14/2015 22:22:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tri_ds_insert]
ON [dbo].[t_DynamicScore]
for INSERT
AS
BEGIN
--定义变量
declare @Teaching int,--教学总分
@Environmental int,--环境总分
@Cars int,--车辆总分
@Teacher int,--教练总分
@sumsum int,--评分总条数
@teacsum int,--对某位教练评论的总条数
@SchoolID varchar(50),--驾校ID
@TeacherID varchar(50);--教师ID
--在inserted表中查询已经插入记录信息
select @SchoolID = SchoolID,@TeacherID = TeacherID from inserted;
--获得教学总分
select @Teaching=SUM(Teaching) from t_DynamicScore where SchoolID=@SchoolID;
--环境总分
select @Environmental=SUM(Environmental) from t_DynamicScore where SchoolID=@SchoolID;
--车辆总分
select @Cars=SUM(Cars) from t_DynamicScore where SchoolID=@SchoolID;
--教练总分
select @Teacher=SUM(Teacher) from t_DynamicScore where TeacherID=@TeacherID;
--获得教学打分的数量
select @sumsum=COUNT(id) from t_DynamicScore where SchoolID=@SchoolID;
--对某位教练评分的总条数
select @teacsum=COUNT(id) from t_DynamicScore where TeacherID=@TeacherID;
--除法，小数点后保留4位，为该驾校最新的教学DSR
update t_DSchool set Teaching=CONVERT(decimal(8,4),@Teaching*1.0000/@sumsum),--这里乘以1.0000就为了得到的数据精确到小数点后4位
Environmental=CONVERT(decimal(8,4),@Environmental*1.0000/@sumsum),
Cars=CONVERT(decimal(8,4),@Cars*1.0000/@sumsum)
where ID=@SchoolID;
update t_Teacher set DSR=CONVERT(decimal(8,4),@Teacher*1.0000/@teacsum) where ID=@TeacherID;
END
————————————————
版权声明：本文为CSDN博主「云上人间钦自赏」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/moniteryao/article/details/42718951