how to create a trigger for a table whose values depends on join

Question

i have these tables from where first i need to find the validity column value from packages table then i need to create a trigger on every insert or update to see if the userpackges table has packagestatus column value set to true then trigger creates buy date from getdate function and expiry date from the value of validity column which is returning 90(days) so what i need to do is to add these 90 days in buydate column and store this value in expiry date
these are the tables

and this is the query i have made so far which is able to set date when packagestatus is set to true
CREATE TRIGGER BuyDate ON UserPackages
after insert
as
update UserPackages set BuyDate=GETDATE() where PackageStatus='true'

Answer 1

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'

Please share your point of view about the above trigger efficiency or inefficiency

Answer 2

Hi @Shahzaib khan ，

We need the statement like 'update....set... from table inner join inserted' to trigger the row of data which is inserted or updated.

Besides, we could not use 'update a set ... from inserted a' directly without join. Otherwise we would get the error 'The logical tables INSERTED and DELETED cannot be updated'.

In addition, we also need to join packages table since we need the value of VALIDITIY to update the value of Expirydate.

Please try with below statement and check whether it is helpful to you:

 CREATE  TRIGGER BuyDateUpdate ON UserPackages  
   AFTER INSERT,UPDATE   
   as  
   set nocount on  
   update a  
   set BuyDate=GETDATE() , Expirydate = dateadd(dd,C.VALIDITIY,getdate())   
   from UserPackages A   
   INNER JOIN inserted B ON A.P_ID=B.P_ID  
   left join packages C on a.P_ID=C.PID  
   where A.packagestatus='true'  

After I performed one update and insert action, we could have below output.

INSERT INTO UserPackages VALUES (2,1,1,'TRUE',NULL,NULL)  
  
UPDATE UserPackages SET packagestatus='TRUE' WHERE P_ID=2  
  
select * from UserPackages  

Output:

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

In triggers you always want to use the inserted and/or deleted tables. https://learn.microsoft.com/en-us/sql/relational-databases/triggers/use-the-inserted-and-deleted-tables?view=sql-server-ver15

CREATE TRIGGER BuyDate ON UserPackages  
after insert  
as  
update u  
set BuyDate=GETDATE()   
FROM inserted i  
  INNER JOIN UserPackages p  
on i.ID = p.ID  
  
where i.PackageStatus='true'