how to update a column value in table with the help of trigger having multiple joins

Question

i have these tables named Users,Packages and UserPackages.

now what i want to do is that i need to have a trigger to check as soon as PackageStatus column in UserPackages table is set to true i need to perform a join then find out if U_ID in UserPackages table came from reference of another user if yes then
perform a join between UserPackages and Packages table using P_ID and get ReferCommission column value and Price column value
then calculate referCommission and store it in the Users Table(from whom's reference the user in UserPackages came) in RefEarning column.
this is the query i have made to see if user came from reference of another user

as you can see U_ID=2 came from reference of U_ID=1 so it shows referedBy as 1 and U_ID=1 hasn't come from any reference so its shows referedBy as null

i have created this cte
with firstCte(Name,UID,PName,Price,ReferComission,ReferredBy)
as
(
select Users.Name,Users.ID,Packages.PName,Packages.Price,Packages.ReferCommission,(
select Users.ID from users inner join Refers on Users.UserRef_No=Refers.RefOf where U_ID=2
)as referedby
from Users inner join UserPackages on UserPackages.U_ID=Users.ID
inner join Packages on Packages.PID=UserPackages.P_ID
where U_ID=2 and UserPackages.PackageStatus='true'
)

update  Users set RefEarning=(select ReferComission from firstCte where ID=ReferredBy)  
   

But there are few issue first i need to add to the RefEarning and not reassign it secondly if i use U_ID=1 its assigning null which i don't want if ReferredBy is Null then simply i don't want to run the update query
last but not the least for now as you can see its adding ReferComission value which is 10% but i don't want that
i want to calculate 10% of the Price of the Pacakage then add it to the RefEarning column

Answer 1

Hi- I am not sure I understood your question clearly. Are you asking something like this?

tblUser
U_ID U_Name dailyEarning
1 Shahza
2 Khan

tblDailyEarningConfig
U_ID DailyYearning
1 70
2 50

UPDATE
tblUser
SET U.dailyEarning = U.dailyEarning+d.DailyYearning
FROM
tblUser U
INNER JOIN tblDailyEarningConfig D
ON d.U_ID = U.U_ID

simply you can add this to a SQL Job then schedule the script.

Ref:
https://www.sqlservertutorial.net/sql-server-basics/sql-server-update-join/

Thanks

Answer 2

Hi @Shahzaib khan ，

If you want to update the dailyEarning column according to different users, you can create a temporary table to update.

1.When the dailyEarning column is in the user table:

create table users  
    (U_ID int,  
    username char,  
    dailyearning int)  
    insert into users values(1,'A',0),(2,'B',0)  

    create table temp  
    (U_ID int,  
    updatevalues int)  
    insert into temp values(1,70),(2,50)  

    update users  
    set dailyearning=dailyearning+updatevalues  
    from users u  
    join temp t  
    on u.U_ID=t.U_ID  

    select * from users  

    drop table users  
    drop table temp  

The result of executing the code twice：

2.When the dailyEarning column is in the other table:

create table other  
(U_ID int,  
dailyearning int)  
insert into other values(1,0),(2,0)  

create table temp  
(U_ID int,  
updatevalues int)  
insert into temp values(1,70),(2,50)  

update other  
set dailyearning=dailyearning+updatevalues  
from other u  
join temp t  
on u.U_ID=t.U_ID  

select * from other  

drop table other  
drop table temp  

If you want to update automatically every day, you need to create a sql server job,Please refer below link for more details:Create a Job

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Best Regards
Echo

Answer 3

As I understand you need a to schedule a procedure that uses merge into to the other tables.

merge-transact-sql

Answer 4

In order to understand your question, please post:

1. CREATE TABLE ...
2. INSERT INTO ... with some sample data
3. Output you expect

Answer 5

this is the trigger i created its calculating expiry date and buydate as soon as PackageStatus changes to true along with this i also calculated refEarning which was my need to calculate and insert as soon as package status changes to true

CREATE TRIGGER MyTrigger ON UserPackages
       after update
       as
             DECLARE @PId INT
               DECLARE *anonymous user* INT
         declare @Val INT
            SELECT @PId = inserted.P_ID  ,*anonymous user*=inserted.U_ID     
            FROM INSERTED
            select @Val=Packages.Validitiy from Packages where Packages.PID=@PId

       update UserPackages
       set BuyDate=GETDATE() , Expirydate = dateadd(dd,@Val,getdate()) 
      where P_ID=@PId and U_ID=*anonymous user* and PackageStatus='True'
      ;
     with firstCte(Name,UID,PName,Price,ReferComission,ReferredBy)
     as
     (
     select distinct Users.Name,Users.ID,Packages.PName,Packages.Price,Packages.ReferCommission,(
     select distinct Users.ID from users inner join Refers on Users.UserRef_No=Refers.RefOf  
     )as referedby 
     from Users inner join UserPackages on UserPackages.U_ID=Users.ID
      inner join Packages on Packages.PID=UserPackages.P_ID
      inner join Refers on Users.Ref_No=Refers.RefOf  
     where U_ID=*anonymous user* and UserPackages.PackageStatus='true' 
     )
     --select  Name,PName,Price,ReferComission,ReferredBy,RefEarning=Price*ReferComission/100 from firstCte 
     -- Define the outer query referencing the CTE name.
     update  Users set RefEarning+= Price*ReferComission/100 from firstCte where ID=ReferredBy ;

     --with secondCte(UID,Name,Active)
     --as
     --(select ID,Name,Active from Users where ID=*anonymous user* and Active='False')
     update Users set Active='True' where ID=*anonymous user* and Active='False'

thankyou all for the help and response

EDIT:- The above trigger fails for more then one row updates...so far can't get it to work for multiple rows
this is the new edited version but still not able to update for more then 1 records as subquery is returning more thn one row and i need some kind of loop to iterate over all of them

USE [TempInvestManage]
GO
/****** Object:  Trigger [dbo].[HeavyTrigger3]    Script Date: 9/13/2020 11:13:44 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[HeavyTrigger3] ON [dbo].[UserPackages]
  after update
  as
  BEGIN
    SET NOCOUNT ON;

    IF UPDATE(PackageStatus)

 begin
with firstCte(Name,UID,RefEarn,Active,PName,Price,ReferComission,Expiry,PackStat,ReferredBy,Val)
as
(
select distinct  Users.Name,Users.ID,Users.RefEarning,Users.Active,Packages.PName,Packages.Price,Packages.ReferCommission,UserPackages.ExpiryDate,UserPackages.PackageStatus,(
select distinct Users.ID from users inner join Refers on Users.UserRef_No=Refers.RefOf  
)as referedby ,(select Packages.Validitiy from Packages where Packages.PID=(select P_ID from inserted))as Val
from Users inner join UserPackages on UserPackages.U_ID=Users.ID
 inner join Packages on Packages.PID=(select P_ID from inserted)
 inner join Refers on Users.Ref_No=Refers.RefOf  
where U_ID=(select U_ID from inserted)  and UserPackages.PackageStatus='true' 
)
 update UserPackages set BuyDate=GETDATE(),ExpiryDate=DATEADD(dd,Val,GETDATE()) from firstCte

 where P_ID=(select P_ID from inserted) and U_ID=(select U_ID from inserted);
 with firstCte(Name,UID,RefEarn,Active,PName,Price,ReferComission,Expiry,PackStat,ReferredBy,Val)
as
(
select distinct  Users.Name,Users.ID,Users.RefEarning,Users.Active,Packages.PName,Packages.Price,Packages.ReferCommission,UserPackages.ExpiryDate,UserPackages.PackageStatus,(
select distinct Users.ID from users inner join Refers on Users.UserRef_No=Refers.RefOf  
)as referedby ,(select Packages.Validitiy from Packages where Packages.PID=(select P_ID from inserted))as Val
from Users inner join UserPackages on UserPackages.U_ID=Users.ID
 inner join Packages on Packages.PID=(select P_ID from inserted)
 inner join Refers on Users.Ref_No=Refers.RefOf  
where U_ID=(select U_ID from inserted)  and UserPackages.PackageStatus='true' 
)
 update  Users set RefEarning+= Price*ReferComission/100 from firstCte where ID=ReferredBy ;

update Users set Active='True' where ID=(select U_ID from inserted) and Active='False'
end;
 end;