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!

• J.D.: 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.
• Nikola: I edited it. I hope it is clearer now.

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