MSSQL Server Trigger

Trigger is used to run sql query after some action in database like delete, instert, update. I created two tables which are SALES and PRODUCTS.


1. CREATE TRIGGER FOR INSERT
This trigger is used to update ProductStockCount after sold product.

CREATE TRIGGER SALE_INSERT ON SALES FOR INSERT
 AS 
BEGIN
 DECLARE @ProductCount int, @ProductID int

 SELECT @ProductCount =ProductCount, @ProductID= ProductID
 FROM inserted 

 UPDATE PRODUCTS 
 SET ProductStockCount= ProductStockCount-@ProductCount
 WHERE ProductID=@ProductID;

END

2.CREATE TRIGGER FOR UPDATE
This trigger will update your product stock count in products table when you update your product count in sales table.

CREATE TRIGGER SALE_UPDATE ON SALES FOR UPDATE AS 
BEGIN
DECLARE @NewProductCount int, @ProductCount int, @ProductID int

SELECT @ProductCount=ProductCount, @ProductID= ProductID 
FROM deleted
SELECT @NewProductCount=ProductCount, @ProductID= ProductID 
FROM inserted

if(@NewProductCount<@ProductCount)
 BEGIN
  UPDATE PRODUCTS 
  SET ProductStockCount= ProductStockCount+(@ProductCount-@NewProductCount)
  WHERE ProductID=@ProductID;
 END
else
 BEGIN
  UPDATE PRODUCTS 
  SET ProductStockCount= ProductStockCount-(@NewProductCount-@ProductCount)
  WHERE ProductID=@ProductID;
 END
END

3.CREATE TRIGGER FOR DELETE
This trigger will update product stock count for deleted sale.

CREATE TRIGGER SALE_DELETE ON SALES FOR DELETE AS 
BEGIN
DECLARE @ProductCount int, @ProductID int

SELECT @ProductCount=ProductCount, @ProductID= ProductID FROM deleted

 UPDATE PRODUCTS SET ProductStockCount= ProductStockCount+@ProductCount WHERE ProductID=@ProductID;
END