Create Trigger warring6
On Wate_quality_montioring --在test表中创建触发器
for insert,update --为什么事件触发
As --事件触发后所要做的事情
DECLARE @dissolved_oxygen float
DECLARE @ammonia_nitrogen float
DECLARE @water_temperature float
DECLARE @turbidity float
DECLARE @conductivity float
DECLARE @pH_value float
DECLARE @COD float
DECLARE @TOC float
DECLARE @water_quality_standard nvarchar(255)
SET @dissolved_oxygen= (select [dissolved_oxygen] from Wate_quality_montioring)
SET @ammonia_nitrogen = (select [ammonia_nitrogen] from Wate_quality_montioring)
SET @water_temperature= (select [water_temperature] from Wate_quality_montioring)
SET @turbidity = (select [turbidity] from Wate_quality_montioring)
SET @conductivity = (select [conductivity] from Wate_quality_montioring)
SET @pH_value= (select [pH_value] from Wate_quality_montioring)
SET @COD = (select [COD] from Wate_quality_montioring)
SET @TOC = (select [TOC] from Wate_quality_montioring)
SET @water_quality_standard= (select [water_quality_standard] from Wate_quality_montioring)
IF @dissolved_oxygen>10
BEGIN
INSERT INTO [dbo].[Historical_warning]
([Alert_type]
,[alert_source]
,[alert_status]
,[alert_time]
,[alert_content])
VALUES
('报警类'
,'水质监测'
,'报警'
,getdate()
,'溶解氧超限预警')
END
IF @ammonia_nitrogen>10
BEGIN
INSERT INTO [dbo].[Historical_warning]
([Alert_type]
,[alert_source]
,[alert_status]
,[alert_time]
,[alert_content])
VALUES
('报警类'
,'水质监测'
,'报警'
,getdate()
,'氨氮超限预警')
END
IF @water_temperature>10
BEGIN
INSERT INTO [dbo].[Historical_warning]
([Alert_type]
,[alert_source]
,[alert_status]
,[alert_time]
,[alert_content])
VALUES
('报警类'
,'水质监测'
,'报警'
,getdate()
,'水温超限预警')
END
IF @turbidity>10
BEGIN
INSERT INTO [dbo].[Historical_warning]
([Alert_type]
,[alert_source]
,[alert_status]
,[alert_time]
,[alert_content])
VALUES
('报警类'
,'水质监测'
,'报警'
,getdate()
,'浊度超限预警')
END
IF @conductivity>10
BEGIN
INSERT INTO [dbo].[Historical_warning]
([Alert_type]
,[alert_source]
,[alert_status]
,[alert_time]
,[alert_content])
VALUES
('报警类'
,'水质监测'
,'报警'
,getdate()
,'电导率超限预警')
END
IF @pH_value>10
BEGIN
INSERT INTO [dbo].[Historical_warning]
([Alert_type]
,[alert_source]
,[alert_status]
,[alert_time]
,[alert_content])
VALUES
('报警类'
,'水质监测'
,'报警'
,getdate()
,'PH值超限预警')
END
IF @COD>10
BEGIN
INSERT INTO [dbo].[Historical_warning]
([Alert_type]
,[alert_source]
,[alert_status]
,[alert_time]
,[alert_content])
VALUES
('报警类'
,'水质监测'
,'报警'
,getdate()
,'COD超限预警')
END
IF @TOC>10
BEGIN
INSERT INTO [dbo].[Historical_warning]
([Alert_type]
,[alert_source]
,[alert_status]
,[alert_time]
,[alert_content])
VALUES
('报警类'
,'水质监测'
,'报警'
,getdate()
,'总有机碳超限预警')
END
IF @water_quality_standard>10
BEGIN
INSERT INTO [dbo].[Historical_warning]
([Alert_type]
,[alert_source]
,[alert_status]
,[alert_time]
,[alert_content])
VALUES
('报警类'
,'水质监测'
,'报警'
,getdate()
,'水质标准超限预警')
END
子查询返回多条，但不知道怎么去处理，网上说定义游标解决 但我不会下滑游标
确保代码里面的子查询返回的结果只有一条记录。
(select [water_quality_standard] from Wate_quality_montioring)，上面这些查询语句返回了多笔记录，只能返回一条记录，加条件控制。
SET @dissolved_oxygen= (select [dissolved_oxygen] from Wate_quality_montioring)
SET @ammonia_nitrogen = (select [ammonia_nitrogen] from Wate_quality_montioring)
SET @water_temperature= (select [water_temperature] from Wate_quality_montioring)
SET @turbidity = (select [turbidity] from Wate_quality_montioring)
SET @conductivity = (select [conductivity] from Wate_quality_montioring)
SET @pH_value= (select [pH_value] from Wate_quality_montioring)
SET @COD = (select [COD] from Wate_quality_montioring)
SET @TOC = (select [TOC] from Wate_quality_montioring)
SET @water_quality_standard= (select [water_quality_standard] from Wate_quality_montioring)可能是上面给变量赋值时，某个查询返回的值不止一个，你自己排查一下吧。
您好，我是有问必答小助手，您的问题已经有小伙伴解答了，您看下是否解决，可以追评进行沟通哦~
如果有您比较满意的答案 / 帮您提供解决思路的答案，可以点击【采纳】按钮，给回答的小伙伴一些鼓励哦～～
ps:问答VIP仅需29元，即可享受5次/月 有问必答服务，了解详情>>>https://vip.csdn.net/askvip?utm_source=1146287632