Is this can be achieved using SQL server triggers?

Question

0.00/5 (No votes)

See more:

, +

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

Onur ERYILMAZ

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Accepted Answer · 2017-11-07T03:27:00

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

SQL

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

Posted 7-Nov-17 3:27am

CHill60

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! :-)