Constraint Logics and Homework

I am stuck at a homework

Objective: Whenever a new sale is made the quantity sold cannot exceed the existing stocked quantity for each of the products that it includes. This means that the sale item(s) that cause the inconsistency should not be kept in the database. Notice that, regarding the calculation of the existing stock, the same product could be taken from different warehouses and/or purchase orders.

To recreate the database, you can use following DDL Code

USE [INVENTORY_fresh]
GO
/****** Object:  Table [dbo].[PURCHASE_ITEMS]    Script Date: 17-12-2020 18:57:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PURCHASE_ITEMS](
    [PO] [numeric](18, 0) NOT NULL,
    [PRODUCT] [numeric](18, 0) NOT NULL,
    [PURCHASE_QUANTITY] [smallint] NOT NULL,
    [IN_STOCK] [smallint] NOT NULL,
    [QUANTITY_SOLD] [smallint] NOT NULL,
 CONSTRAINT [PK_PURCHASE_ITEMS] PRIMARY KEY CLUSTERED 
(
    [PO] ASC,
    [PRODUCT] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[PURCHASE_ORDERS]    Script Date: 17-12-2020 18:57:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PURCHASE_ORDERS](
    [IDPURCHASEORDER] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [WAREHOUSE] [numeric](18, 0) NOT NULL,
    [PURCHASE_DATETIME] [datetime] NOT NULL
) ON [PRIMARY]
GO

/****** Object:  Index [PK_PURCHASE_ORDERS]    Script Date: 17-12-2020 18:57:45 ******/
ALTER TABLE [dbo].[PURCHASE_ORDERS] ADD  CONSTRAINT [PK_PURCHASE_ORDERS] PRIMARY KEY NONCLUSTERED 
(
    [IDPURCHASEORDER] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PURCHASE_ITEMS]  WITH CHECK ADD  CONSTRAINT [FK_PURCHASE_ITEMS_PO] FOREIGN KEY([PO])
REFERENCES [dbo].[PURCHASE_ORDERS] ([IDPURCHASEORDER])
GO
ALTER TABLE [dbo].[PURCHASE_ITEMS] CHECK CONSTRAINT [FK_PURCHASE_ITEMS_PO]
GO
ALTER TABLE [dbo].[PURCHASE_ITEMS]  WITH CHECK ADD  CONSTRAINT [FK_PURCHASE_ITEMS_PRODUCTS] FOREIGN KEY([PRODUCT])
REFERENCES [dbo].[PRODUCTS] ([PRODUCTCODE])
GO
ALTER TABLE [dbo].[PURCHASE_ITEMS] CHECK CONSTRAINT [FK_PURCHASE_ITEMS_PRODUCTS]
GO
ALTER TABLE [dbo].[PURCHASE_ORDERS]  WITH CHECK ADD  CONSTRAINT [FK_PO_WAREHOUSE] FOREIGN KEY([WAREHOUSE])
REFERENCES [dbo].[WAREHOUSES] ([WAREHOUSECODE])
GO
ALTER TABLE [dbo].[PURCHASE_ORDERS] CHECK CONSTRAINT [FK_PO_WAREHOUSE]
GO

If you want to download full Script with data, to create environment - https://drive.google.com/file/d/1vY1nJk1Dm7K3k1VNERgk3V6LieLIqlWw/view?usp=sharing

What I have done so far?

I have created a constraint that will ensure the QUANITY_SOLD is not more than total sum of IN_STOCK for every product

ALTER TABLE [dbo].[PURCHASE_ITEMS]  WITH CHECK ADD  CONSTRAINT [Sold_not_greater_stock] CHECK  (([dbo].[totalstock]([product])>=[quantity_sold]))
GO

ALTER TABLE [dbo].[PURCHASE_ITEMS] CHECK CONSTRAINT [Sold_not_greater_stock]
GO

Additionally, I made a rule that the IN_STOCK columns will not become A NEGATIVE VALUE

ALTER TABLE [dbo].[PURCHASE_ITEMS]  WITH CHECK ADD  CONSTRAINT [cantbenegative] CHECK  (([in_stock]>=(0)))
GO

ALTER TABLE [dbo].[PURCHASE_ITEMS] CHECK CONSTRAINT [cantbenegative]
GO

After that, I created a trigger that will update IN_STOCK and QUANITITY_SOLD

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE   TRIGGER [DBO].[TEST_TRIGGER] ON [DBO].[PURCHASE_ITEMS] FOR UPDATE
AS BEGIN

    SET NOCOUNT ON;
    DECLARE @PRODUCT INT
    DECLARE @PO INT
    DECLARE @QUANITY_SOLD_OLD INT 
    DECLARE @QUANITY_SOLD_NEW INT 

    SELECT @PO = PO, @PRODUCT = PRODUCT, @QUANITY_SOLD_OLD = QUANTITY_SOLD FROM DELETED
    SELECT @QUANITY_SOLD_NEW = QUANTITY_SOLD FROM INSERTED WHERE PRODUCT = @PRODUCT AND PO = @PO
    IF @QUANITY_SOLD_OLD < @QUANITY_SOLD_NEW
    BEGIN
        UPDATE PURCHASE_ITEMS SET IN_STOCK = IN_STOCK - (@QUANITY_SOLD_NEW-@QUANITY_SOLD_OLD)
            WHERE PRODUCT = @PRODUCT AND PO = @PO
    END
    ELSE
    BEGIN
        UPDATE PURCHASE_ITEMS SET IN_STOCK = IN_STOCK + (@QUANITY_SOLD_OLD-@QUANITY_SOLD_NEW)
            WHERE PRODUCT = @PRODUCT AND PO = @PO
    END
END

GO

ALTER TABLE [DBO].[PURCHASE_ITEMS] ENABLE TRIGGER [TEST_TRIGGER]
GO

The Problem

While my trigger can update IN_STOCK and QUANTITY_SOLD, its limited to one row.

Suppose there are 11 same products in warehouse 1 and 45 in warehouse 2 and quanitity_sold is 12, my trigger should update IN_STOCK for warehouse 1 as 0 and warehouse 2 as 44.

I'm not sure if I was able to explain my question clearly enough but I tried my best. Please help me.