title: Trigger to Insert, Update, Delete Stock Qty in SQL Server
body: <p>I have 3 tables as Figure below:
  <img src="https://i.stack.imgur.com/JsvTC.jpg" alt="Table Figure"></p>
  
  <p>My scenario is when there is insert, update or delete tblGRDetail, then it's update StockQty field in tblSPStock.</p>
  
  <p>Here is my trigger:</p>
  
  <pre><code>CREATE TRIGGER [dbo].[StockUpdate] on [dbo].[tblGRDetail]
  FOR INSERT,DELETE,UPDATE
  AS
  BEGIN
  IF EXISTS(SELECT * FROM inserted INNER JOIN tblGR ON inserted.GRNo=tblGR.GRNo WHERE tblGR.DealerCode=(SELECT DealerCode FROM tblSPStock WHERE DealerCode=tblGR.DealerCode) AND inserted.PartCode=(Select PartCode from tblSPStock WHERE PartCode=inserted.PartCode)) AND EXISTS (SELECT * FROM deleted INNER JOIN tblGR ON deleted.GRNo=tblGR.GRNo WHERE tblGR.DealerCode=(SELECT DealerCode FROM tblSPStock WHERE DealerCode=tblGR.DealerCode) AND deleted.PartCode=(Select PartCode from tblSPStock WHERE PartCode=deleted.PartCode))
  BEGIN
  UPDATE tblSPStock
  SET StockQty=StockQty-(Select GRQty From deleted INNER JOIN tblGR ON deleted.GRNo=tblGR.GRNo WHERE tblSPStock.DealerCode=tblgr.DealerCode AND tblSPStock.PartCode=deleted.PartCode)+(Select GRQty From inserted INNER JOIN tblGR ON inserted.GRNo=tblGR.GRNo WHERE tblSPStock.DealerCode=tblGR.DealerCode AND tblSPStock.PartCode=inserted.PartCode)
  WHERE tblSPStock.DealerCode=(select DealerCode from inserted INNER JOIN tblGR ON inserted.GRNo=tblgr.GRNo)
  AND tblSPStock.PartCode=(select PartCode from inserted)
  AND tblspstock.PartCode=(select PartCode from deleted)
  END
  
  IF EXISTS(SELECT * FROM inserted INNER JOIN tblGR ON inserted.GRNo=tblGR.GRNo WHERE tblGR.DealerCode=(SELECT DealerCode FROM tblSPStock WHERE DealerCode=tblGR.DealerCode) AND inserted.PartCode=(Select PartCode from tblSPStock WHERE PartCode=inserted.PartCode)) AND NOT EXISTS (SELECT * FROM deleted INNER JOIN tblGR ON deleted.GRNo=tblGR.GRNo WHERE tblGR.DealerCode=(SELECT DealerCode FROM tblSPStock WHERE DealerCode=tblGR.DealerCode) AND deleted.PartCode=(Select PartCode from tblSPStock WHERE PartCode=deleted.PartCode))
  BEGIN
  INSERT INTO tblSPStock (PartCode,
                          StockQty,
                          DealerCode)
  SELECT PartCode, GRQty, tblGR.DealerCode from inserted inner join tblGR on inserted.GRNo=tblgr.GRNo
  END
  
  IF NOT EXISTS(SELECT * FROM inserted INNER JOIN tblGR ON inserted.GRNo=tblGR.GRNo WHERE tblGR.DealerCode=(SELECT DealerCode FROM tblSPStock WHERE DealerCode=tblGR.DealerCode) AND inserted.PartCode=(Select PartCode from tblSPStock WHERE PartCode=inserted.PartCode)) AND EXISTS (SELECT * FROM deleted INNER JOIN tblGR ON deleted.GRNo=tblGR.GRNo WHERE tblGR.DealerCode=(SELECT DealerCode FROM tblSPStock WHERE DealerCode=tblGR.DealerCode) AND deleted.PartCode=(Select PartCode from tblSPStock WHERE PartCode=deleted.PartCode))
  BEGIN
  UPDATE tblSPStock
  SET StockQty=StockQty-(Select GRQty From deleted)
  WHERE tblSPStock.DealerCode=(select DealerCode from deleted INNER JOIN tblGR ON deleted.GRNo=tblgr.GRNo)
  AND tblspstock.PartCode=(select partcode from deleted)
  END
  END
  </code></pre>
  
  <p>When i insert into tblGRDetail it showed error below:
  <strong>Subquery returned more than 1 value. This is not permitted when the subquery foolows=, !=, &lt;, &lt;=,>,>= or whenthe subquery is ude as an epression</strong></p>
  
  <p>Any one here have any idea to this error pls!</p>
  
  <p><strong>==UPDATED==</strong></p>
  
  <p>Now i use MERGE in my trigger, but when i delete tblGRDetail it's not update StockQty.
  but if i remove INNER JOIN tblGR g ON d.GRNo=g.GRNo, it's OK.</p>
  
  <pre><code>CREATE TRIGGER [dbo].[DeleteStock] on [dbo].[tblGRDetail]
  FOR DELETE, UPDATE
  AS
  BEGIN
  SET NOCOUNT ON;
      MERGE tblSPStock
      Using (
          SELECT
              d.PartCode,
              d.GRQty,
              g.DealerCode
          FROM deleted d INNER JOIN tblGR g ON d.GRNo=g.GRNo
      ) MergeData ON tblSPStock.PartCode= MergeData.PartCode
                     AND tblSPStock.DealerCode=MergeData.DealerCode
           WHEN MATCHED THEN 
          UPDATE SET
          tblSPStock.StockQty=tblSPStock.StockQty-MergeData.GRQty;
  END
  </code></pre>
  
  <p>for insert, update is ok, problem only delete. </p>
  
  <p>Pls. your help is appropriated.</p>