# How to run both "before" and "after" update in trigger

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?

• Isn't that simply `NEW.foo - OLD.foo` ? And that expression could be in either a BEFORE or an AFTER Trigger. Oct 7, 2021 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, 2021 at 7:47
• Instead of Triggers, write a Stored Procedure to do the necessary processing. Oct 9, 2021 at 20:21
• Have to write trigger only. Oct 10, 2021 at 0:21