After Insert Trigger Oracle to Track the Changes Done by the User
question
how to create a trigger for a table whose values depends on join
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'
A minor change to accommodate expiry date.(Not tested)
CREATE TRIGGER BuyDate ON UserPackages after insert as update u set BuyDate=GETDATE() , Expirydate = dateadd(dd,90,getdate()) FROM inserted i INNER JOIN UserPackages p on i.ID = p.ID where i.PackageStatus='true' Please mark this reply as answer if it solved your issue or vote as helpful if it helped.
Blog
LinkedIn
0 Votes 0 ·
thanks it works with little changes but i didn't get why you used join as its not needed for this
CREATE TRIGGER BuyDateUpdate ON UserPackages after update as update UserPackages set BuyDate=GETDATE() , Expirydate = dateadd(dd,90,getdate()) where PackageStatus='true' the problem is i want to get this 90 value from packages table which is in validity column of packages table
so i need to perform a join using PID and then get days value from the join and then place it in place of 90 as every package will have different expiry date
0 Votes 0 ·
Hi @Shahzaibkhan-7817，
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.
DDL I created and used:
create table packages ( PID INT IDENTITY(1,1), PNAME VARCHAR(20), ADDURATION INT, VALIDITIY INT, DAILYEARNING INT ) INSERT INTO packages (PNAME,ADDURATION,VALIDITIY,DAILYEARNING) values ('Starter',15,90,70), ('Starter Plus',15,90,220) create table UserPackages ( id INT, U_ID INT, P_ID INT, packagestatus VARCHAR(10), BUYDATE DATETIME, EXPIRYDATE DATETIME ) INSERT INTO UserPackages VALUES (1,1,1,'FALSE',NULL,NULL), (7,2,2,'FALSE',NULL,NULL) Best regards
Melissa
0 Votes 0 ·
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 @UId INT declare @Val INT SELECT @PId = inserted.P_ID ,@UId=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=@UId 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=@UId 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=@UId and Active='False') update Users set Active='True' where ID=@UId and Active='False' Please share your point of view about the above trigger efficiency or inefficiency
Hi @Shahzaibkhan-7817,
Actually we have not the same environment with you. So we could not test this trigger from our side.
The best method to validate one trigger efficiency or inefficiency is to execute this create trigger statement and manually make some updates on UserPackages table to see the result and performance.
Please also remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.
Best regards
Melissa
0 Votes 0 ·
actually the trigger works fine for one update only but fails if try to update more then one rows at a time
0 Votes 0 ·
As I said in my previous post, you likely need to use the virtual table "inserted" to operate only on the updated rows. Your trigger as written is updating every row in the table every time a row is updated. That is not efficient and not necessary.
0 Votes 0 ·
question details
2 people are following this question.
Related Questions
After Insert Trigger Oracle to Track the Changes Done by the User
Source: https://docs.microsoft.com/answers/questions/85837/how-to-create-a-trigger-for-a-table-whose-values-d.html
Hi @Shahzaibkhan-7817，
Could you please validate all the answers so far and provide any update about this?
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.
Thank you for understanding!
Best regards
Melissa
0 Votes 0 ·