Lim Min Yi Lim Min Yi - 27 days ago 8
SQL Question

Attribute A based on Attribute B (Microsoft SQL Server 2014)

For example I have two tables named

Manager_Order
and
Book_Inventory
.

Manager_Order(
ID (PK),
Book_Name,
Quantity (Attribute A)
)

Book_Inventory(
ID (PK),
Book_Name (FK on Manager_Order),
Quantity (Attribute B)
)


Everytime manager makes an order, I want the Quantity in
Book_Inventory
updates automatically.

Answer

Look into using an after update trigger.

Here is an example (not tested):

CREATE TRIGGER [dbo].Update_Book_Inventory
ON [dbo].Manager_Order  
FOR  UPDATE
AS
BEGIN
    SET NOCOUNT ON;
      UPDATE [dbo].Book_Inventory
        SET Quantity = i.Quantity
            FROM [dbo].Book_Inventory
            INNER JOIN INSERTED i ON [dbo].Book_Inventory.Book_Name = i.Book_Name
END

Reference: https://www.mssqltips.com/sqlservertip/4024/sql-server-trigger-after-update-for-a-specific-value/