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
相关产品推荐
相关产品推荐

