hello,
i am working on a trigger that checks if a user has a record in a table for a certain product.

1 If not insert a new record based on some criteria
2. If exisits, update it with the additional subscriptions.

NOTE: Records coudl be from 1 - 6 in value.

The code is basically a loop

something like this

Code:
select @product_update = (select count(product) from 

Accounting_users_purchased goods where username = @user and product = @product--this product should be a variable representing products 1-6)

--The above line checks to see if the entry @product(which could be 1,2 ,3 to 6) exists then runs the codes below, then 
if @product_update = 0 
insert into Accounting_users_PurchasedGoods (product,quantity, username)
values (@item1,@quantity1,@user)
else update Accounting_users_PurchasedGoods 
set quantity = (select quanity1 from Accounting_financials_PO_invoice where invoice_num = @invoiceID and username = @user)
How do i

1. Get this code to loop based on the values gotten in the above line in red.

NOTE: It checks to see if the products 1- 6 exists
2. Secondly, it updates the Accounting_users_purchasedGoods tables for each entry.

3. Do i use a union all operator to join multiple tables or just simply rewrite each code ??

thanks
Afrika