I'm new to programming and I'm doing a project for myself to consolidate the material. I tried all the methods, but without success. How to correctly write a trigger that, after entering data in the second table in the MOVING_QUANTITY attribute, automatically subtracted SP_PRODUCT_QUANTITY from the first table from MOVING_QUANTITY in the second. And the new result was already recorded in SP_PRODUCT_QUANTITY. In other words, a replacement for the first. I wanted to implement an idea where, for example, I transfer a certain amount of goods and enter the quantity I need, and he took the quantity of a certain product from the warehouse table. Below I have given the tables, and my own trigger, but it does not work correctly as I wanted.

 CREATE TABLE Storage_Prod_List
 (
 STORAGE_PROD_ID     INT           PRIMARY KEY IDENTITY(1, 1),
 FK_SP_STORAGE_ID    INT,
 FK_SP_PRODUCT_ID    INT,
 SP_PRODUCT_QUANTITY    INT        NOT NULL
 );

 CREATE TABLE Moving_list
 (
 MOVING_ID           INT           PRIMARY KEY IDENTITY(1, 1),
 MOVING_DATE         DATE         NOT NULL,
 MOVING_PRODUCT      INT,
 MOVING_QUANTITY     INT           NOT NULL,
 FROM_STORAGE        INT,
 TO_SHOP             INT,
 );


 CREATE TRIGGER UpdateQuantity
 ON Moving_list
 AFTER INSERT
 AS 
 BEGIN
    UPDATE st
    SET SP_PRODUCT_QUANTITY = SP_PRODUCT_QUANTITY - (SELECT MOVING_QUANTITY FROM  Moving_list)
    FROM Storage_Prod_List st
    JOIN (SELECT MOVING_ID, SUM(MOVING_QUANTITY) AS Quantity FROM INSERTED GROUP BY   MOVING_ID) i ON st.STORAGE_PROD_ID = FK_SP_PRODUCT_ID
 END;

I've been sitting here for days, but I can't think of anything.

The result I want to achieve:

Result:

CodePudding user response：

You are referencing the wrong column name in the trigger.

Your join condition is wrong. You are not joining to the column in Moving_list at all

ON st.STORAGE_PROD_ID = FK_SP_PRODUCT_ID

You are subtracting SP_PRODUCT_QUANTITY with all MOVING_QUANTITY from table Moving_list. It will result in error when the Moving_list table contains more than 1 row

SET SP_PRODUCT_QUANTITY = SP_PRODUCT_QUANTITY 
                        - (SELECT MOVING_QUANTITY FROM  Moving_list)

The sub query should be group by MOVING_PRODUCT not MOVING_ID

GROUP BY   MOVING_ID

The corrected trigger :

CREATE TRIGGER UpdateQuantity
 ON Moving_list
 AFTER INSERT
 AS 
 BEGIN
    UPDATE st
    SET    SP_PRODUCT_QUANTITY = st.SP_PRODUCT_QUANTITY 
                               - i.Quantity
    FROM   Storage_Prod_List st
    JOIN   (
               SELECT MOVING_PRODUCT, SUM(MOVING_QUANTITY) AS Quantity 
               FROM   INSERTED 
               GROUP BY MOVING_PRODUCT
           ) i ON st.FK_SP_PRODUCT_ID = i.MOVING_PRODUCT
 END;