心得:mssql 相除结果为0 如果是列 一定不能是int型。如果是定义的变量。一定不要定义为int ,可以是float
USE [database] GO /****** Object: Trigger [dbo].[UpdateVotePercent] Script Date: 06/29/2016 09:09:43 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author:<Author,,Name> -- Create date: <Create Date,,> -- Description:<Description,,> -- ============================================= ALTER TRIGGER [dbo].[UpdateVotePercent] ON [dbo].[vote_option] AFTER UPDATE AS declare @subjectid int;--定义状态 declare @optionid int;--定义状态 declare @subjectCount float;--投票总投票数 declare @optionall float;--得到当前投票项投票合计 if update([votenumber]) BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; select @subjectid=subject_id,@optionid=id from inserted; select @subjectCount=votenumber from vote_subject where id=@subjectid;--得到总投票数 select @optionall=SUM(votenumber) from vote_option where id=@optionid;--得到当前投票项投票数 --更新投票百分比 -- 测试用insert into temp (optcount,subcount,optid,p) values(@optionall,@subjectCount,@optionid,cast(round(((@optionall)/(@subjectCount)),2) as numeric(18,2)) ); if @subjectCount<>0 begin update vote_option set votepercent=Convert(decimal(10,2),(votenumber/@subjectCount)) where subject_id=@subjectid; --print @optionall/@subjectCount; end -- Insert statements for trigger here END
Copyright © 2004-2024 Ynicp.com 版权所有 法律顾问:建纬(昆明)律师事务所 昆明市网翼通科技有限公司 滇ICP备08002592号-4