Ayman Ayman - 4 months ago 9
SQL Question

How to update table based on values in another table

I was wondering according to the below schema

How can i keep inventory out-stock values updated according to product Qty values in

orderDetail
table.

for example i have the below sample data

OrderDetail

Order-id Product-id Qty
1 1 5


Inventory

Inventory-id Date Product-id In-stock Out-stock stock-left
1 1/1/2016 1 10 5 5


lets say in Order-detail table value of Qty is changed to 1 how can i changed it to 1 also in Inventory table.

Schema
enter image description here

Answer

You can use triggers to propagate the values

CREATE TRIGGER [dbo].[inventory_update] ON [dbo].[OrderDetail]
    FOR UPDATE
    AS
    BEGIN

        UPDATE Inventory
        SET OutStock = inserted.Qty
        FROM inserted
        WHERE Inventory.inventory-id = inventory-id

    END
Comments