You need to enable JavaScript to run this app. SQL Server DML触发器多值子查询报错及支付删除未联动删支票排查
优惠活动
大模型
产品
解决方案
定价
更多

SQL Server DML触发器多值子查询报错及支付删除未联动删支票排查

SQL Server DML触发器联动开发问题说明

业务场景基础

  • 开发环境:SQL Server 实例
  • 开发目标:为2张业务表的删除、更新场景实现联动逻辑
  • 涉及3张关联业务表:
    • payments_yearly:存储全部支付主记录
    • payments_yearly_details:存储支付对应的明细信息(包含支付方式、金额),支持的支付方式包括现金、Visa、支票
    • cheque_in:存储支票类的专属业务数据
  • 现有业务查询逻辑:按客户ID聚合查询支付主信息、汇总支付明细的总金额,实现SQL为:
SELECT dbo.payments_yearly.ID, SUM(dbo.payments_yearly_details.value) AS value, dbo.payments_yearly.note,
dbo.payments_yearly.date, dbo.payments_yearly.app_user FROM dbo.payments_yearly INNER JOIN
dbo.payments_yearly_details ON dbo.payments_yearly.ID = dbo.payments_yearly_details.pay_id where payments_yearly.con_id=@con_id
GROUP BY dbo.payments_yearly.ID, dbo.payments_yearly.con_id, dbo.payments_yearly.note, dbo.payments_yearly.date, dbo.payments_yearly.app_user

已开发触发器及测试异常

触发器1:支付主表删除时联动删除关联支票数据

  • 设计目标:当用户从payments_yearly表删除某条支付记录时,自动从cheque_in表删除该支付关联的全部支票数据
  • 实现代码:
ALTER TRIGGER [dbo].[delete_cheque_after_delete_payment] on [dbo].[payments_yearly]
AFTER Delete
AS 
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

Declare @payid as int
set @payid= (select ID from payments_yearly_details where pay_id=(SELECT deleted.ID FROM deleted) and method =N'شيك')
Delete from cheque_in where pay_id =@payid

END
  • 测试现象:
    • 从payments_yearly表删除任意支付记录时,关联的支票数据并未被删除,无法定位问题根源
    • 尝试将触发器中关联查询得到的@payid值替换为cheque_in表中已存在的静态值(如25),删除操作可正常执行,但会与第二个触发器产生冲突

触发器2:支票数据变更时联动更新支付明细金额

  • 设计目标:当用户修改支票金额、删除与某笔支付关联的支票时,自动更新payments_yearly_details表中对应支票类支付方式的记录金额(该金额可能由多张关联支票的金额汇总计算得到)
  • 实现代码:
ALTER TRIGGER [dbo].[Update_payments_yearly_detailson_cheque_UpdateDelete] on [dbo].[cheque_in]
after UPDATE, DELETE
AS 
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

IF EXISTS (SELECT 1 FROM payments_yearly_details WHERE ID=(SELECT deleted.pay_id FROM deleted)) 
BEGIN
Declare @value as decimal(10,2)
set @value= (SELECT SUM(cheque_in.cheque_value) from cheque_in where cheque_in.pay_id = (SELECT deleted.pay_id FROM deleted))
Update payments_yearly_details set value=@value where ID=(SELECT deleted.pay_id FROM deleted)
END
END
  • 测试现象:
    • 单独测试该触发器时,支票金额修改、支票删除场景下的逻辑运行正常,可正确联动更新支付明细的对应金额
    • 两个触发器联动执行时,抛出如下运行时错误:
System.Data.SqlClient.SqlException: 'Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.'

待排查核心问题

作为触发器开发新手,需明确两个问题的产生根源:

  • 第一个联动删除支票的触发器为何无法正常生效
  • 将第一个触发器的关联值替换为静态值后,为何会与第二个触发器联动触发上述子查询返回多值的错误

内容的提问来源于stack exchange,提问作者Rabeea qabaha

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.24 05:15:04