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

Shahzaib khan 6 Reputation points
2020-08-26T08:36:15.463+00:00

i have these tables named Users,Packages and UserPackages.
22485-u.png22486-p.png22487-up.png

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
22549-jq.png

22550-jq2.png

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

{count} votes

5 answers

Sort by: Most helpful
  1. Rajith Alwis 6 Reputation points
    2020-08-26T11:30:27.34+00:00

    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

    No comments

  2. EchoLiu-MSFT 14,416 Reputation points
    2020-08-27T05:39:31.167+00:00

    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:
    20745-image.png
    20777-image.png
    20778-image.png

    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


  3. Stratos Matzouranis 26 Reputation points
    2020-08-26T13:01:18.087+00:00

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

    merge-transact-sql

    No comments

  4. Guoxiong 7,681 Reputation points
    2020-08-26T18:33:15.94+00:00

    In order to understand your question, please post:

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

  5. Shahzaib khan 6 Reputation points
    2020-09-08T04:19:17.13+00:00

    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;
    
    No comments