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

Jun Harefa

15y2.1kVisual Basic .NET

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.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Pravin More15y ago
Accepted answer
hi Jun,You can create trigger on Update and Insert ............like this............

CREATE TRIGGER Stock_Update ON UPDATE,INSERT ON tb_inventory
AS BEGIN
UPDATE inv
SET inv.item_stock = (ord.order_quantity - u.use_quantity)
FROM tb_inventory as inv
JOIN tb_order AS ord ON ord.id_order = inv.id_order
Join tb_use as u ON u.id_use=inv.id_use
END

so this will update inventory table when u update or insert to Oder and Use table....

i hope this will definatly solve your problem.

Thank You,
Pravin.
Jun Harefa15y ago
Thank you so much Pravin.. you give me an inspiration to create the trigger. I create the trigger like this:

CREATE TRIGGER Stock_Update
BEFORE INSERT ,UPDATE ON tb_inventory
FOR EACH ROW
BEGIN
SET NEW.item_stock = (tb_order.order_quantity - tb_use.use_quantity)
FROM NEW JOIN tb_order ON tb_order.id_order = NEW.id_order
JOIN tb_use ON tb_use.id_use = NEW.id_use
END;$$
Delimiter$$

It work..