How to get a table automatically updated when another table is updated Using C#

How to get a table automatically updated when another table is updated Using C#

Hi all, I have 4 (four) tables using MySql database, ie:

tb_item (
id_item, (varchar) --> PK
item_name, (varchar)
price, (double)
)

tb_order (
id_order, (varchar) --> PK
id_item, (varchar) -->FK
date_order, (date)
lead_time, (float)
order_quantity, (double)
)

tb_use (
id_use, (varchar) --> PK
id_item, (varchar) -->FK
date_use, (date)
use_quantity, (double)
)

tb_inventory (
id_inventory, (varchar) --> PK
id_item, (varchar) --> FK
id_order, (varchar) --> FK
id_use, (varchar) --> FK
item_stock, (double)
)

My problem is I want to 开发者_JAVA技巧item_stock in tb_inventory automatically updated: when tb_order.order_quantity or tb_use.use_quantity are updated, then tb_inventory.item_stock will updated automatically by using a calculation “tb_order.order_quantity – tb_use.use_quantity = tb_inventory.item_stock” (Example: 10 - 7 = 3). and vice versa. How to create code for the problem in C#.

Please help, thanks in advance.

You can do it using triggers, but it would be better to normalize your database so that this information is stored only in one place. If you use triggers and somewhow the database is incorrectly updated, that error can persist for a long time unnoticed.

An alternative is to create a view which calculates the amount of stock by querying the other tables. This will give a slight performance hit because the value will have to be recalculated every time it is requested. This performance hit can be partially solved by adding indexes to make the recalculation very fast. In the end it depends on whether you are more interested in raw speed, or consistency and correctness of the result.