Subquery returned more than 1 value when trigger run

Question

I'm trying to create DML triggers to do certain things on delete and update in 2 tables,

I have 3 tables: payments_yearly, payments_yearly_details,cheque_in

payments_yearly table have all payments and those payments have details( payments methods and values) saved in payments_yearly_details, the payments method can be cash, visa, cheque. cheque details will be saved in the cheque_in table.

I have a query that gets the payment info + the sum value from details as this:

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

so I Created a trigger to delete all the cheques from the cheque_in table if the user deletes a payment from the payments_yearly table and my trigger as bellow:

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

but when I delete any payment from the payments_yearly the cheque didn't delete also, So I don't know where is the mistake here. I tried to change the @pay_id value to a static value from the cheque_in table like 25 but it conflicts with another trigger that I created to update the payments_yearly_details table value of the payment method cheque "because this value may come from multiple cheques" when user change a cheque value or deleted a cheque related to the payment, and this trigger as bellow:

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

this trigger working good and its updating the payment value when deleting a cheque or update a cheque value, but its conflict with the first trigger with this error message : 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.'

I'm new to triggers, So my question is: why is my first trigger not working? and why I'm getting the error message on the second trigger if I assigned a static value(which is in the cheque and in this case my trigger working) to the first trigger.

Thank you.

Answer 1

Your issue is this:

 (select ID from payments_yearly_details where pay_id=(SELECT deleted.ID FROM deleted) and method =N'شيك')

If this statement returns more than one value you get the error you mentioned. You cant do this with scalar sub queries. I mean outside of the fact the system throws hard error for it - think about it - my variable now is equal to this and that? That doesnt make sense.

You can do cheap hacks to fix it like using DISTINCT or GROUP BY but that doesnt fix the logical side of things.

EDIT

so you just pointed out your issue:

 (SELECT deleted.ID FROM deleted)

if you delete more than one ID at a time youre going to get this error.

EDIT 2

So now we know you have multiple id's per row and we know that how youre trying to assign to a variable doesnt work, so try this.

Chnage this:

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

To this:

Delete from cheque_in where pay_id IN ((select ID from payments_yearly_details where pay_id IN ((SELECT deleted.ID FROM deleted)) and method =N'شيك'))