MS SQL Creating Tables and Adding Values by a Trigger

Question

Table_A is an existing table which has 3 columns Id (PK), Name, Stock. I would like to create a trigger to create a table.

For example, Table_A has an item, Id (PK) = 3, Name = Apple, Stock = 10. By using triggers I would like to create a new table called Table_B and when I write the following code "INSERT INTO Table_B VALUES(3,5)" a new table will be created and it will have 2 columns ProductId and Quanity. At the same time the value for Stock for Table_A will be decreased by 5. Every time I insert something to Table_B I would like them to get added to Table_B and the stock in Table_A to be decreased by the quantity of that item inserted with the related ProductId. Thanks!

Answer 1

If you have a [Product] table and a [ProductTransaction] table, and your goal is to have [Product].[Quantity] always be the sum of the transaction table, then your trigger will need to take into account INSERT, UPDATE, and DELETE operations. You will also need to ensure that nobody ever directly updates the [Product].[Quantity] value, as it will result in inconsistencies.

One way to get around this without using a trigger would be to use a view. For example:

CREATE OR REPLACE VIEW vw_inventory AS
SELECT p.[ID], p.[Name], SUM(txn.[Quantity]) AS [Quantity]
  FROM [Product] p INNER JOIN [ProductTransaction] txn ON p.[ID] = txn.[ProductID]
 GROUP BY p.[ID], p.[Name]

Then you can query off the view:

SELECT * FROM [vw_inventory]
 WHERE [ID] = 4;

Otherwise, if you really want to use a trigger, then you'll need to set an AFTER trigger on the transaction table:

CREATE TRIGGER [trg_product_quantity]
    ON [ProductTransaction]
 AFTER INSERT, UPDATE, DELETE
    AS
 BEGIN
    SET NOCOUNT ON;
    UPDATE [Product] p 
       SET [Quantity] = tmp.[Quantity]
      FROM (SELECT z.[ProductID], SUM(z.[Quantity]) as [Quantity]
              FROM [ProductTransaction] txn 
             WHERE txn.[ProductID] IN (inserted.[ProductID], deleted.[ProductID])
             GROUP BY z.[ProductID]) tmp 
     WHERE tmp.[ProductID] = p.[ID];
   END

Note: Be sure to double-check and modify this SQL code. I may have mixed in a little MySQL/PostgreSQL by mistake.

Hope this gives you something to think about 👍🏻