Subquery returned more than 1 value even when they don't return anything

Question

I have a DB which is connected like this.

I am writing a WPF app to do simple insert/delete/update stuff to tables.

The problem goes like this:

1. I get an Advertisement object and try to insert it into the DB

   public static int Insert(Advertisement Advertisement)
   {
       Database db = new Database();
       db.Connect();
       SqlCommand command = db.CreateCommand(SQL_INSERT);
       PrepareCommand(command, Advertisement);
       int ret = db.ExecuteNonQuery(command);
       db.Close();
       return ret;
   }
   

The SQL_INSERT

public static String SQL_INSERT = "INSERT INTO \"Advertisement\" VALUES (@IDProduct," +
        " @CampaignDescription, @CampaignStart, @CampaignEnd)";

IDAdvertisement is an IDENTITY.

I have a trigger set on Advertisement table INSERT

ALTER trigger [dbo].[AutomaticUserAdvertisementScoreCalculating]
ON [dbo].[Advertisement]
AFTER INSERT
AS
begin
begin transaction;
declare Users cursor for 
Select "User".IDUser, Sum(Price), inserted.IDProduct from "User"
join Purchase as pu on "User".IDUser = pu.IDUser  
join PurchaseProduct as pp on pu.IDPurchase = pp.IDPurchase
join Product as pr on pp.IDProduct = pr.IDProduct
join inserted on pr.IDProduct = inserted.IDProduct
where pr.ProductType = (select ProductType from Product
          join Advertisement on Product.IDProduct = Advertisement.IDProduct
          join inserted on Advertisement.IDProduct = inserted.IDProduct
          where Advertisement.IDAdvertisement = inserted.IDAdvertisement)
GROUP BY "User".IDUser, inserted.IDProduct
HAVING Sum(Price) > 50;
declare @IDUser int;
declare @Price decimal;
declare @IDProduct int;
open Users;
fetch next from Users into @IDUser,@Price, @IDProduct;
while @@FETCH_STATUS=0
begin
    insert into UserAdvertisementScore values(@IDUser, (select IDAdvertisement from Advertisement where IDProduct = @IDProduct), @Price);
    fetch next from Users into @IDUser, @Price, @IDProduct;
end
close Users;
deallocate Users;
commit;

end

I don't know why, but depending on the IDProduct used, it might throw out this error.

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.

Please tell me if you need any more information on this

Answer 1

I didn't feel that was really a duplicate. Sure the error was the same thing but the real issue in this question was that the trigger was majorly flawed. Pretty sure your entire trigger body could simplified to something pretty close to this. And you should NOT use transactions inside a trigger. There is already a transaction in progress and anything you do will cause the original transaction to get out of whack.

insert UserAdvertisementScore
( --or whatever the column names are. You should ALWAYS specify the columns for an insert
    [User]
    , Price
    , IDProduct
)
Select u.IDUser
    , Sum(Price)
    , i.IDProduct 
from [User] u
join Purchase as pu on u.IDUser = pu.IDUser  
join PurchaseProduct as pp on pu.IDPurchase = pp.IDPurchase
join inserted i on pr.IDProduct = i.IDProduct
join Product as pr on pp.IDProduct = pr.IDProduct 
GROUP BY u.IDUser
    , i.IDProduct