Need help creating trigger to update vales in different tables

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

So, ive got a relational database, and im trying to create triggers that basically update other fields..

To provide more information. I have a big database, but shall only provide q couple of tables as the other fields are just employee tables etc and are unnecessary.

The tables ill include are Order and Stock..

Order (OrderID, ProductID, ItemQuantity)
Stock (StockID, ProductID, StockQuantity)

Try not to focus on nit picking my database, i just want to create a working functioned trigger.

I basically want to create a trigger than updates the StockQuantity from the Stock table by taking the ItemQuantity in the order table away from StockQuantity. How do i go about doing this? It will take away the quantity from the stock level that will be based on the productID.. So essentially something like...

Trigger = StockQuantity = StockQuantity - ItemQuantity where ProductID in Stock is same as ProductID in Order.

Posted 13-Feb-15 8:59am

Chris_Burns

Add a Solution

Comments

PIEBALDconsult 13-Feb-15 15:37pm

Triggers are generally not a good idea.

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)

ZurdoDev · Accepted Answer · 2015-02-13T09:25:00

Solution 1

You just need to do a join in your trigger.

Something like this should work:

SQL

UPDATE Stock
SET StockQuantity = StockQuantity - o.ItemQuantity
FROM Stock s
INNER JOIN inserted i ON s.ProductID = i.ProductID
INNER JOIN Order o ON s.ProductID = o.ProductID

But my guess is you have lots of orders so you may have to do something like

SQL

UPDATE Stock
SET StockQuantity = StockQuantity - SUM(o.ItemQuantity)
FROM Stock s
INNER JOIN inserted i ON s.ProductID = i.ProductID
INNER JOIN Order o ON s.ProductID = o.ProductID

Inner Join to the inserted table in your trigger so that only those records that are being changed will be updated. Or, remove inserted to do the entire table.

Posted 13-Feb-15 9:25am

ZurdoDev

Updated 13-Feb-15 9:41am

v2

Comments

PIEBALDconsult 13-Feb-15 15:38pm

If it's in a trigger, shouldn't it JOIN to the INSERTED table ?

ZurdoDev 13-Feb-15 15:40pm

Yes, sorry, got carried away and forgot this was a trigger. Was just giving user part of everything.

I'll update it. Thanks.

[no name] 13-Feb-15 15:54pm

This is my trigger code altogether.

CREATE OR REPLACE TRIGGER stock_trigger on [dbo].[Order]
FOR INSERT AS
UPDATE Stock
SET StockQuantity = StockQuantity - o.ItemQuantity
FROM Stock s
INNER JOIN inserted i ON s.ProductID = i.ProductID
INNER JOIN [Order] o ON s.ProductID = o.ProductID
GO

[no name] 13-Feb-15 15:51pm

It seems to work, but it seems to work wrong. Basically each time a record is inserted on the Orders table, i want the quantity to be taken away from the StockQuantity in the Stock Table. It seems to take it away, but it seems to be taking away different numbers each time.

ZurdoDev 13-Feb-15 15:59pm

Ya, it's going to keep decrementing unless you somehow set a flag as to which ones have already been decremented.

Perhaps you really want a trigger on Order so that when an order is created/modified, then reduce quantity.

[no name] 13-Feb-15 16:00pm

Yes, a trigger is created for Order. But i dont seem to understand how to make it work.

ZurdoDev 13-Feb-15 16:14pm

OK. Sorry, I had misread some of it at the beginning.

So, when an order is inserted you want to decrement the stock count in another table.

Try this:

UPDATE Stock
SET StockQuantity = StockQuantity - i.ItemQuantity
FROM Stock s
INNER JOIN inserted i ON s.ProductID = i.ProductID -- inserted here is the order table

[no name] 13-Feb-15 16:21pm

That works perfectly thank you. Can you just give me a run through of what you did and what part does what? I'd like to learn from this, so i don't have to bother people in the future :)

ZurdoDev 13-Feb-15 16:24pm

It's 4 lines so it's actually pretty simple. I had confused things originally so I apologize for that.

Line 1: You're going to update the stock table
Line 2: You're going to change the StockQuantity to be equal to the StockQuantity value that's already there minus the ItemQuantity on the Order
Line 3: When update statements require joins you need the first FROM table
Line 4: Joins the inserted table which is a built-in table in triggers and is a copy of the record(s) that is being changed.