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;
--教练总分
SQLServer 新增数据时触发器自动计算并更新相关值
最新推荐文章于 2024-08-01 17:10:09 发布
创建了一个名为tri_ds_insert的SQLServer触发器,当向t_DynamicScore表中插入数据时,该触发器会自动计算教学、环境和车辆的总分,并根据评价条数求平均值。平均值保留四位小数,更新到对应的驾校(t_DSchool)和教练(t_Teacher)的DSR(Dynamic Score Rating)字段。
摘要由CSDN通过智能技术生成