Last post Aug 27, 2020 07:22 PM by mgebhard
Aug 27, 2020 06:43 PM|Learner94|LINK
i have few tables and i want to make a join then perform few checks and then run a stored procedure via a trigger that runs on daily basis.
i will share the tables
this is user table
this is packages table
and this is userpackages table
now what i need to do is that as U_ID=2 and U_ID=3 came from the reference of U_ID=1 so on daily basis i need to run a trigger that will execute a stored procedure which will first check if U_ID=1 account has any package active(he needs to have any package
active before he is eligible to get referal bonus)
next if U_ID=2 and U_ID=3 came through reference of U_ID=1 then until and unless both these U_ID=2 and U_ID=3 activate any package U_ID=1 will not receive the Referal bonus.
but there are few conditions for eg if U_ID=3 came by reference of U_ID=1 and U_ID=3 has package 7(Buisness) then U_ID=1 will get referal commission of 12% of the price of the package 7(Buisness) but this needs to be done only one time so if U_ID gets comission
once then after that he will not get it again we need to put some check as our trigger needs to run on daily basis it will keep on adding referal commission of 12% to U_ID=1 which isn't the requirement.
so i need two things first a stored procedure do above all things and second a trigger that runs automatically on daily basis
also i need to know if any changes/additions in the database tables are required.
looking forward to the answer
plus is it better to use a trigger and do it via stored procedure or should i be using some logic in asp.net mvc to check as soon as any User buys a package his packagestatus will become true then see if this User came from reference of another user and
from whose reference this user came also has any packagestatus active then send referal comission to him/her?
for e.g U_ID=2 came from reference of U_ID=1 now as soon as U_ID=2 buys any package then execute a logic to see if U_ID=1 has packagestatus=true then give him the commission otherwise not.
if U_ID=2 buys and activate a package before then U_ID=1 from whom reference he came then i need to execute some logic to check as soon as U_ID=1 buys any package then check if any other user who came using his reference(which in this case is U_ID=2) also
has a packagestatus=true then transfer referral commission of that user(U_ID=2)package to the U_ID=1
so from what i searched and seen i got to know godaddy won't support triggers so i cannot use a trigger to daily execute the stored procedue
so only option left is to somehow do the required task every time user buys a package.
Aug 27, 2020 07:22 PM|mgebhard|LINK
SQL server has the ability to run scheduled jobs. A job is any logic you wish to execute including a stored procedure.