Automatic insertion of calculated values to another

Question

I am creating an application, so I need trigger or function that will help automatically enter data in the database as data is entered into the system considering the other table's primary key.

I currently have AddItems, Sales and Products tables. The AddItems table is where all the invoice data is stored. What I need is to be able to add the quantity of products together according to the Item_Code. Once the sum is found the data should be stored on the Products table. This should happen every time a product product is added in the AddItems table.

AddItems

Item_ID (PK, IDENTITY)
Date
Supplier_Name
Track_NO
Quantity
Item_Code
Item_Description
Vat
Amount
Total_Amount

Products

Product_Id (Identity)
Item_Code (PK)
Item_Description
Available_Quantity

Query I managed to come up with needs to be manually ran to insert the values. Below is the statement used.

INSERT INTO Products
(Item_Code, Item_Description, Available_Quantity)
SELECT Item_Code, Item_Description,
    Sum(Quantity)
FROM AddItems
Group By Item_Code, Item_Description;

(Asked the same question but i couldn't merge my account with the other)

Answer 1

I've reduced your table schema in this way:

CREATE TABLE AddItems
(
Item_ID int IDENTITY PRIMARY KEY,
Quantity int NOT NULL,
Item_Code varchar(10) NOT NULL,
Item_Description varchar(100) NOT NULL
);

CREATE TABLE Products
(
Product_Id int IDENTITY PRIMARY KEY,
Item_Code varchar(10) NOT NULL,
Item_Description varchar(100) NOT NULL,
Available_Quantity int NOT NULL DEFAULT(0)
);

You can add a trigger like next one:

CREATE TRIGGER trg_AddItems_Insert
ON AddItems
FOR INSERT
AS
BEGIN

    /* update existing products */
    UPDATE   p
    SET      Available_Quantity = Available_Quantity + Qty
    FROM     Products p
    JOIN     (SELECT   Item_code, SUM(Quantity) Qty
              FROM     inserted
              GROUP BY Item_Code) s
    ON       p.Item_Code = s.Item_Code;


    /* insert new products */
    INSERT INTO Products
    (Item_Code, Item_Description, Available_Quantity)
    SELECT   i.Item_Code, i.Item_Description, Sum(Quantity)
    FROM     inserted i
    WHERE NOT EXISTS(SELECT 1 
                     FROM Products 
                     WHERE Item_Code = i.Item_Code)
    GROUP BY i.Item_Code, i.Item_Description;

END
GO

This solution assumes AddItems table won't be modified or deleted, it is only used to add new rows. If some user can UPDATE or DELETE rows, you should consider either:

• Add one trigger for DELETE and another for UPDATE.
• Use stored procedures to INSERT/UPDATE/DELETE rows.

Now, let me insert some values:

INSERT INTO AddItems VALUES 
(10, 'AAA', 'PRODUCT A'),
(5,  'AAA', 'PRODUCT A'),
(7,  'CCC', 'PRODUCT C'),
(1,  'BBB', 'PRODUCT B');

INSERT INTO AddItems VALUES 
(25, 'AAA', 'PRODUCT A'),
(10, 'BBB', 'PRODUCT B'),
(3,  'CCC', 'PRODUCT C'),
(5,  'CCC', 'PRODUCT C');

And this is the final result:

SELECT * FROM PRODUCTS;
GO

Product_Id | Item_Code | Item_Description | Available_Quantity
---------: | :-------- | :--------------- | -----------------:
         1 | AAA       | PRODUCT A        |                 40
         2 | BBB       | PRODUCT B        |                 11
         3 | CCC       | PRODUCT C        |                 15

db<>fiddle here

Answer 2

The problem with any such automated solution is that they tend to get very complicated quickly and there are so many ways for the value to get out of synch. You must have code in Insert, Delete and Update triggers to cover every contingency. You must also have at least Insert and Update triggers on the Products table to verify the values are entered/modified only under defined conditions. That's a lot of work to implement and even more work for maintenance and testing.

If that is the only solution, then that's what you do and you just have to commit to all that work.

Fortunately, there is a very simple solution: a view.

If the purpose of the Products table is merely to expose the Available_Quantity value, the entire table can be replaced with a view defined by the Select statement in your own sample Insert statement.

If the Products table has other uses and the Products_ID key is used/referenced by other tables or code, then drop the Available_Quantity field altogether. Then create a view (ProductsQty) exposing the Product_ID field:

create view ProductsQty as
SELECT  p.Product_ID, p.Item_Code, p.Item_Description,
        IFNULL( Sum( ai.Quantity ), 0 ) as Available_Quantity
FROM    Products P
LEFT JOIN AddItems ai
    ON  ai.Item_Code = p.Item_Code
    AND ai.Item_Description = p.Item_Description;

If most current queries against the Products table needs the quatity value, you may want to change the name of Products table to something like ProductsRaw and name the view Products. That way, existing code that access that table don't have to be changed.

A view greatly simplifies the database design/maintenance and guarantees the value will always be accurate.