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!

  • 1
    Could you please clarify your goals a little bit better? For example, using actual table names (even if you just call them TableA, TableB, and TableC) when you reference each event you want to occur will make your question immensely easier to follow.
    – J.D.
    Apr 29, 2021 at 1:29
  • I edited it. I hope it is clearer now.
    – Nikola
    Apr 29, 2021 at 2:47

1 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:

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]
    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];

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 👍🏻

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.