单位提交监控点报告时产生预警v0.1（资金超资）
set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER trigger [tri_inspect_warn_update]on [dbo].[projectReport_inspectInfo] after updateasdeclare @id nvarchar(50),@moduleId nvarchar(50),@projectId nvarchar(50),@status nvarchar(50),@createPerson nvarchar(50),@inspectCompleteRate float,@sumScheduleRate float,@sumFundRate float,@warnType nvarchar(10)declare @description nvarchar(500),@topicId nvarchar(50),@schedule nvarchar(50),@insecptName nvarchar(50)--删除前不是暂存，删除后不为暂存的--状态不是暂存退回和通过（经信委操作）if ((select inspect.status from Inserted inspect) not in('408080b52b895b4a012b89d840e10012','408080b52b895b4a012b89d8b15e0014','408080b52b895b4a012b89d8dcc30015'))beginselect @moduleId=inspect.id,@projectId=inspect.projectInfoId,@status=inspect.status,@inspectCompleteRate=inspect.inspectCompleteRate,@sumScheduleRate=inspect.sumScheduleRate@sumFundRate=inspect.sumFundRate,@createPerson=inspect.fillPerson from Inserted inspectif(@inspectCompleteRate<100)--该进度小于100beginset @id=CONVERT(varchar(50), newid())--insert into dbo.projectWarn_warn(id,projectInfoId,moduleId,warnName,warnType,warnTime,planValue,status,createPerson)--values(@id,@projectId,@moduleId,'0','0',getdate(),@opValue,'0',@createPerson)select @topicId=project.projectType from dbo.establish_projectInfo as project where project.id=@projectIdif(@topicId='408080b52b2e4df1012b2e530aac0003')--课题类编号beginselect @schedule=inspect.planSchedule from dbo.projectReport_inspectInfo as inspect where inspect.projectInfoId=@projectId and inspect.id=@moduleIdselect @insecptName=iset.name from dbo.projectReport_inspectSet iset where iset.id =(select inspectSetId from dbo.establish_topicPlanSchedule schedule where schedule.id=@schedule)endelsebeginselect @schedule=inspect.planSchedule from dbo.projectReport_inspectInfo as inspect where inspect.projectInfoId=@projectId and inspect.id=@moduleIdselect @insecptName=iset.name from dbo.projectReport_inspectSet iset where iset.id =(select inspectSetId from dbo.establish_nonTopicPlanSchedule schedule where schedule.id=@schedule)end set @description=@insecptName+'阶段';if exists(select * from dbo.projectWarn_warn where moduleId=@moduleId and warnType=1)update dbo.projectWarn_warn set warnTime=getdate(),factValue=@inspectCompleteRate,createPerson=@createPerson where projectInfoId=@projectId and moduleId=@moduleId and warnType='1'elseinsert into dbo.projectWarn_warn(id,projectInfoId,moduleId,warnName,warnType,warnTime,factValue,description,status,createPerson)values(@id,@projectId,@moduleId,'1','1',getdate(),@inspectCompleteRate,@description,'0',@createPerson)end--else--begin--delete from dbo.projectWarn_warn where projectInfoId=@projectId and moduleId=@moduleId and warnType='1'--endif(@sumFundRate>100)--总体资金百分比beginset @id=CONVERT(varchar(50), newid())--insert into dbo.projectWarn_warn(id,projectInfoId,moduleId,warnName,warnType,warnTime,planValue,status,createPerson)--values(@id,@projectId,@moduleId,'0','0',getdate(),@opValue,'0',@createPerson)select @topicId=project.projectType from dbo.establish_projectInfo as project where project.id=@projectIdif(@topicId='408080b52b2e4df1012b2e530aac0003')--课题类编号beginselect @schedule=inspect.planSchedule from dbo.projectReport_inspectInfo as inspect where inspect.projectInfoId=@projectId and inspect.id=@moduleIdselect @insecptName=iset.name from dbo.projectReport_inspectSet iset where iset.id =(select inspectSetId from dbo.establish_topicPlanSchedule schedule where schedule.id=@schedule)endelsebeginselect @schedule=inspect.planSchedule from dbo.projectReport_inspectInfo as inspect where inspect.projectInfoId=@projectId and inspect.id=@moduleIdselect @insecptName=iset.name from dbo.projectReport_inspectSet iset where iset.id =(select inspectSetId from dbo.establish_nonTopicPlanSchedule schedule where schedule.id=@schedule)end set @description=@insecptName+'阶段';if exists(select * from dbo.projectWarn_warn where moduleId=@moduleId and warnType=0)update dbo.projectWarn_warn set warnTime=getdate(),factValue=@sumFundRate,createPerson=@createPerson where projectInfoId=@projectId and moduleId=@moduleId and warnType='0'elseinsert into dbo.projectWarn_warn(id,projectInfoId,moduleId,warnName,warnType,warnTime,factValue,description,status,createPerson)values(@id,@projectId,@moduleId,'1','0',getdate(),@sumFundRate,@description,'0',@createPerson)end--else--begin--delete from dbo.projectWarn_warn where projectInfoId=@projectId and moduleId=@moduleId and warnType='0'--endend--if ((select inspect.status from Inserted inspect) in('408080b52b895b4a012b89d8b15e0014','408080b52b895b4a012b89d8dcc30015'))--如果为暂存和通过,删除预警.--begin--select @moduleId=inspect.id,@projectId=inspect.projectInfoId,@status=inspect.status,@inspectCompleteRate=inspect.inspectCompleteRate,@sumFundRate=inspect.sumFundRate,@createPerson=inspect.fillPerson from Inserted inspect--delete from dbo.projectWarn_warn where projectInfoId=@projectId and moduleId=@moduleId --end?