Fire trigger on relevant orderno Update,delete,insert

Question

Below is data, i want ,when i do changes in specific orderno qty in table #OrderDetail ,then relevant orderno qty get sum and update in #SalesOrder column O_QTY.

CREATE TABLE #SalesOrder (OrderNo INT,Order_Ref_No VARCHAR(50), Order_date date,O_QTY int); 
 CREATE TABLE #OrderDetail (IDOD INT,OrderNO int,CodeItem int,orderqty int);
 INSERT INTO #SalesOrder VALUES 
 (001,'A_01','01-05-2019',null)  , 
 (002,'B_01','01-05-2019',null)  , 
 (003,'C_01','01-05-2019',null);  
 INSERT INTO #OrderDetail VALUES    (1,001,1,5)  , 
 (2,001,2,3)  , 
 (3,001,3,2)  , 
 (4,002,10,4)  , 
 (5,002,11,3)  , 
 (6,002,3,2)  , 
 (7,003,1,4)  , 
 (8,003,2,3)  , 
 (9,003,3,2);

Trigger

Create TRIGGER [dbo].SO_QTY    ON  [dbo].[OrderDetail]    AFTER INSERT,DELETE,UPDATE AS  BEGIN     UPDATE dbo.SalesOrder   	SET O_QTY=(SELECT SUM(Orderdetail.orderqty)  	FROM dbo.OrderDetail   	--Join inserted I on OrderDetail.OrderNO=I.OrderNO 	WHERE SalesOrder.Orderno=OrderDetail.OrderNO 	and OrderDetail.Del_ID is null 	 GROUP BY OrderDetail.OrderNO)  END 

Answer 1

Hi @Analyst_SQL

Try this:

 CREATE OR ALTER TRIGGER [dbo].SO_QTY    
 ON OrderDetail   
 AFTER INSERT,DELETE,UPDATE 
 AS  
 BEGIN     
  WITH CTE AS
  (SELECT OrderNO,SUM(QTY)AS QTY
   FROM (SELECT OrderNO,SUM(ISNULL(orderqty,0))AS QTY FROM inserted I GROUP BY OrderNO
         UNION
         SELECT OrderNO,SUM(ISNULL(orderqty,0)) * (-1) FROM deleted D GROUP BY OrderNO
        )U
   GROUP BY OrderNO
  )
  UPDATE SalesOrder
  SET O_QTY = ISNULL(O_QTY,0)+ISNULL(QTY,0) 
  FROM SalesOrder S LEFT JOIN CTE C ON S.OrderNo=C.OrderNO
 END

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @Analyst_SQL

I tested on my side, see below:

INSERT INTO SalesOrder VALUES (002,'B_01','01-05-2019',null);  
INSERT INTO OrderDetail VALUES (4,002,10,4) , (5,002,11,3) , (6,002,3,2);

Initial output:

Change 1: INSERT INTO OrderDetail VALUES (7,002,11,5)

Output:

Change 2: DELETE FROM OrderDetail WHERE IDOD = 7

Output:

Change 3: UPDATE OrderDetail SET orderqty=12 WHERE IDOD=6 --update orderqty from 2 to 12

Output: