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

最新推荐文章于 2024-07-11 03:11:25 发布

原创最新推荐文章于 2024-07-11 03:11:25 发布 · 5.5k 阅读

3 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

SQLServer

创建了一个名为tri_ds_insert的SQLServer触发器，当向t_DynamicScore表中插入数据时，该触发器会自动计算教学、环境和车辆的总分，并根据评价条数求平均值。平均值保留四位小数，更新到对应的驾校(t_DSchool)和教练(t_Teacher)的DSR(Dynamic Score Rating)字段。

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