SQL Server: Automatic Insertion of Calculated Values

database-designsql serversql-server-2012

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)

Best Answer

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

Related Question