0

I need to calculate prior values in a table before the column values are updated. Basically, I have to calculate the difference between the before the update and after update values. If there is any difference then I have to update the different tables depending upon the change.

Movie Table 

Movie_Name || Due_Date   || Date_Returned || Late_Fee_per_day 
007        || 07-07-2010 || 09-07-2010    || 2
008        || 10-07-2010 || 10-07-2010    || 3 


Table After the update(Due Date and Date Returned can be updated)

Movie_Name || Due_Date   || Date_Returned || Late_Fee_per_day 
007        || 07-07-2010 || 11-07-2010    || 2
008        || 10-07-2010 || 10-07-2010    || 3 


Customer Table(before update)

Movie_Name || Cust_Name  || Balance  
007        || Jack       || 10 
008        || Sunil      || 25


Customer Table(after update of movie table)

Movie_Name || Cust_Name  || Balance  
007        || Jack       || 2 
008        || Sunil      || 25
 

Now I want to do all this in a single trigger. Both Due_date and Date_Returned of the movie table can be updated. Then I have to calculate that if the calculated late values are different based on the update then I have to update the Customer table based on the difference.

**Calculations**
For movie 007
Late fee before update = 2*(Date_Returned - Due_Date)
                       = 2*(2) [Diff is 2 days)
                       = 4

Late fee after update  = 2*(Date_Returned(11-07-2010) - Due_Date(07-07-2010))
                       = 2*(4) [Diff is 4 days)
                       = 8

Since there is a difference between both the late fee. Now we will update the customer balance in the customer table

Balance after the update for Jack is = Initial Balance - Late Fee(Fee which is calculated above)
                                     = 10 - 8
                                     = 2  

So can anyone please help regarding on how to move forward and write this trigger in Mysql?

4
  • 2
    Isn't that simply NEW.foo - OLD.foo ? And that expression could be in either a BEFORE or an AFTER Trigger.
    – Rick James
    Oct 7 at 3:59
  • Not able to understand completely. Can you please share more briefly or some documentation from where I can get the hunch. Oct 8 at 7:47
  • Instead of Triggers, write a Stored Procedure to do the necessary processing.
    – Rick James
    Oct 9 at 20:21
  • Have to write trigger only. Oct 10 at 0:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.