Click here to Skip to main content
13,665,017 members
Rate this:
 
Please Sign up or sign in to vote.
Hi;

For example I have 3 tables in a SQL Server database. Like this; Database Scheme

When a new row added to the 'OutgoingStockProducts' table, the Quantity will decrease in 'Stock' table by StockCode.
When a row deleted from the 'OutgoingStockProducts' table, the Quantity will increase in 'Stock' table by StockCode.

I can do these by using SQL Trigger.

But how can I do this; When the EntryDate setted in 'OutgoingStockInfoForms' table all associated 'OutgoingStockProducts' 's quantites taken and increased in 'Stock' table by StockCode ?

What I have tried:

I think this can be achieved by using cursors but this is not a good solution I think, and I really don't want to use cursors.
Posted 6-Nov-17 21:08pm
Comments
CHill60 7-Nov-17 8:43am
   
You are correct - don't use cursors.
You can use Triggers to solve the problem.
But it sounds as if you need the SQL to update the OutgoingStockProducts table by the quantity in the Stock table?

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

You appear to be familiar with triggers but just in case here are a few articles you might find useful:
Triggers -- SQL Server[^]
Overview of SQL Server database Triggers[^]
Be Very Careful When You Write SQL Trigger[^]

You have mentioned using cursors but not why you might think you would need one - however all you need to do is join to the Inserted table within the trigger ... e.g. something like this perhaps
UPDATE A
SET A.Quantity = A.Quantity + C.Quantity
FROM OutgoingStockProducts A
INNER JOIN Inserted B ON A.FormID = B.FormID
INNER JOIN Stock C ON A.StockCode = C.StockCode

If this is way off the mark then comment below and I will try to help further
  Permalink  
Comments
Onur ERYILMAZ 7-Nov-17 16:15pm
   
Firstly, thank you for your response.

I want to do this;
Update dbo.Stock SET dbo.Stock.Quantity += dbo.OutgoingStockProducts.Quantity WHERE dbo.Stock.StockCode = dbo.OutgoingStockProducts.StockCode
every product in OutgoingStockProducts that match the 'FormID'.

Further example;
Assume that "SELECT * FROM dbo.OutgoingStockProducts WHERE FormID = 5"
returns 3 rows.
All I want to do is increase the dbo.Stock.Quantity for these items according to the dbo.OutgoingStockProducts.Quantity.

I examine your query and I think the query below might work?

UPDATE dbo.Stock
SET dbo.Stock.Quantity += dbo.OutgoingStockProducts.Quantity
FROM dbo.OutgoingStockProducts
INNER JOIN inserted ON dbo.OutgoingStockProducts.FormID = inserted.FormID
INNER JOIN dbo.Stock ON dbo.OutgoingStockProducts.StockCode = dbo.Stock.StockCode

Thank you again.
CHill60 8-Nov-17 3:50am
   
That looks ok to me - give it a try! :-)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web04-2016 | 2.8.180810.1 | Last Updated 7 Nov 2017
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100