Uğurcan Erkal Uğurcan Erkal - 6 months ago 15
SQL Question

inserting from a table to another table and activeting trigger

INSERT INTO SaleItem (ProdId, SaleQuantity)
SELECT ProdId, BasketProdQuantity FROM Basket;

CREATE TRIGGER TRG_Stock_Decrease
ON SaleItem
AFTER INSERT
AS
DECLARE @ProdId INT
DECLARE @SaleQuantity INT
SELECT @ProdId=ProdId,@SaleQuantity=SaleQuantity FROM inserted
UPDATE Product SET ProdStockQuantity=ProdStockQuantity-@SaleQuantity WHERE ProdId=@ProdId
UPDATE Product SET ProdNoOfSold =ProdNoOfSold+@SaleQuantity WHERE ProdId=@ProdId
DELETE FROM Basket Where ProdId = @ProdId


When I insert datas from Basket to SaleItem it works well, but trigger only work for first id that inserted. How can I make it work for all ids?

EDIT!
I changed my trigger like this and it kinda worked I guess.

CREATE TRIGGER TRG_Stock_Decrease
ON SaleItem
AFTER INSERT
AS
BEGIN
UPDATE Product SET ProdStockQuantity = ProdStockQuantity - (Select SaleQuantity From inserted Where Product.ProdId = inserted.ProdId ) Where Product.ProdId IN (Select ProdId From inserted)
UPDATE Product SET ProdNoOfSold =ProdNoOfSold + (Select SaleQuantity From inserted Where Product.ProdId = inserted.ProdId ) Where Product.ProdId IN (Select ProdId From inserted)
DELETE FROM Basket WHERE Basket.ProdId IN (Select ProdId From inserted)
END


Is that correct?

Answer

No need for each row or nested selects, just inner join with inserted :)

CREATE TRIGGER TRG_Stock_Decrease
    ON SaleItem
AFTER INSERT
AS
    UPDATE P 
    SET ProdStockQuantity=ProdStockQuantity-i.SaleQuantity,
        ProdNoOfSold =ProdNoOfSold+i.SaleQuantity 
    FROM Product P
    INNER JOIN inserted i
        ON P.ProdId=i.ProdId


    DELETE B
    FROM Basket B
    INNER JOIN inserted i
        ON i.ProdId = B.ProdId
Comments