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,@Environmental*1.0000/@sumsum),Cars=CONVERT(decimal(8,@Cars*1.0000/@sumsum)
						 where ID=@SchoolID;
	update t_Teacher set DSR=CONVERT(decimal(8,@Teacher*1.0000/@teacsum) where ID=@TeacherID;
END

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!