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?
NEW.foo - OLD.foo
? And that expression could be in either a BEFORE or an AFTER Trigger.