CODEWITHSUNDEEP
sql-servertriggers
Jr. Hoang
Jr. Hoang

Reputation: 5

Update 2 tables with trigger

I have two tables:

I want to change quantity of product table when quantity of order table was added. But this does not work.

Here is my trigger:

CREATE TRIGGER [dbo].[trg_updatequantity]
On [dbo].[order]
FOR UPDATE
AS
    IF UPDATE(quantity)
       UPDATE product 
       SET product.quantity = product.quantity - (inserted.quantity - deleted.quantity)
       FROM (deleted 
       INNER JOIN inserted ON deleted.IDOrder = inserted.IDOrder) 
       INNER JOIN product ON product.productId = inserted.ProductId

Upvotes: 0

Views: 928

Answers (1)

Alfaiz Ahmed
Alfaiz Ahmed

Reputation: 1728

Try This

Create trigger [dbo].[trg_updatequantity]
On [dbo].[order]
FOR INSERT
AS

update product SET quantity= a.quantity - b.quantity
from product a 
inner join (
    SELECT  ProductID, SUM( ISNULL( quantity, 0))quantity 
    FROM    inserted 
    GROUP BY ProductID) b 
ON  a.ProductID = b.ProductID

Upvotes: 1

Related Questions