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

[junharefa]

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 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).
How to create code for the problem in C#.

Please help, thanks in advance.

[Mitja Bonca]

Hi,
tb_order.order_quantity or tb_use.use_quantity are updated, you can then create another command to update item_stock, like:

SqlConnection sqlConn = new SqlConnection("connString");
SqlCommand cmd = new SqlComand();
cmd.ComamndText = @"UPDATE tb_order SET order_quantity = @quantity WHERE (create a condition here"; //no parentheses!
cmd.Connection = sqlConn;
sqlConn.Open();
cmd.ExecuteNonQuery() //do 1st update
//then:
cmd = new SqlComand();
cmd.CommandText = @"UPDATE tb_inventory SET item_stock WHERE (create a conditon here)"; //no parentheses
cmd.ExecuteNonQuery(); //do 2nd update
//close IDisposable objects:
cmd.Dispose();
sqlConn.Dispose();

But you can still use some stored procedure, where you can define both update in one procedure. But i would do it this way, like I showed.

[junharefa]

Hi Mitja... Thank you for your response..

Can you teach me how to create a stored procedure to update the table? I do not know about stored procedure. I am using MySql database. Thanks for your help.