Update stock - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Update stock

Author

Topic

indr4w
Starting Member

27 Posts

Posted - 2013-05-30 : 03:14:22

i have a master table stock (codeitem,stock) and will update with from transaction table (id, codeitem,qty_in).

example
codeitem stock
001 100
002 50

transaction table
id codeitem qty_in
1 001 50

how this change the current stock with trigger, if qty_in changed become 50 with 60 or anything number.

Thx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-30 : 03:49:14

write an insert trigger in transaction table for this

like


CREATE TRIGGER StockUpdate
ON transaction
AFTER INSERT 
AS
BEGIN
INSERT INTO stock
SELECT codeitem,
SUM(qty_in)
FROM INSERTED i
WHERE NOT EXISTS (SELECT 1 FROM stock WHERE codeitem = i.codeitem)
GROUP BY codeitem

UPDATE s
SET s.stock = s.Stock + i.Totalqty
FROM Stock s
INNER JOIN (SELECT codeitem,SUM(qty_in) AS TotalQty
            FROM INSERTED 
            GROUP BY codeitem)i
ON i.codeitem = s.codeitem 
END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

indr4w
Starting Member

27 Posts

Posted - 2013-05-30 : 04:12:16

Suppose we have a stock of 100 for codeitem 001, and get in stock 2, so the stock to 102. If the incoming number was changed to 3, how to update the stock with a trigger.

sorry about my bad english

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-30 : 04:21:50

quote:
Originally posted by indr4w

Suppose we have a stock of 100 for codeitem 001, and get in stock 2, so the stock to 102. If the incoming number was changed to 3, how to update the stock with a trigger.

sorry about my bad english

for that you need to make it an insert update trigger


CREATE TRIGGER StockUpdate
ON transaction
AFTER INSERT ,UPDATE
AS
BEGIN
INSERT INTO stock
SELECT codeitem,
SUM(qty_in)
FROM INSERTED i
WHERE NOT EXISTS (SELECT 1 FROM stock WHERE codeitem = i.codeitem)
GROUP BY codeitem

UPDATE s
SET s.stock = s.Stock + i.Totalqty
FROM Stock s
INNER JOIN (SELECT codeitem,SUM(qty_in) AS TotalQty
            FROM INSERTED 
            GROUP BY codeitem)i
ON i.codeitem = s.codeitem 
END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

indr4w
Starting Member

27 Posts

Posted - 2013-05-30 : 04:55:26

Mr. Visakh

i have syntax like this:

CREATE TRIGGER G_StockUpdate
ON dbo.g_TDBarangMasuk
AFTER INSERT ,UPDATE
AS
BEGIN
DECLARE @Jumlah int
DECLARE @kodebarang varchar(30)
DECLARE @LBarcode varchar(30)
DECLARE @Satuan varchar(10)
DECLARE @Konversi int
INSERT INTO MBarang
SELECT @KodeBarang=KodeBarang, @Jumlah=SUM(qty), @Lbarcode=Barcode, @Satuan=satuan FROM INSERTED i
WHERE NOT EXISTS (SELECT 1 FROM MBarang WHERE KOdeBarang = i.KodeBarang)
GROUP BY KodeBarang
SELECT @Konversi=isi FROM g_KonversiSatuan WHERE kodebarang=@KodeBarang AND satuan=@Satuan

UPDATE s
SET s.Mbarang = s.MBarang + (i.Totalqty*@konversi)
FROM Mbarang s
INNER JOIN (SELECT @KodeBarang=KodeBarang, @Jumlah=SUM(qty) AS Totalqty, @Lbarcode=Barcode, @Satuan=satuan FROM INSERTED
GROUP BY KodeBarang) i
ON i.kodebarang = s.kodebarang
END

Where i click check syntax getting error :
"Error 199: An Insert Statement cannot containt a Select statement that assigns values to a variable"

what the fault?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-30 : 04:57:25

The syntax is wrong . As specified by error message you cant mix INSERT with SELECT which assigns value to variables

What was the problem with my earlier suggestion?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

indr4w
Starting Member

27 Posts

Posted - 2013-05-30 : 05:25:53

How do I compare, qty old with the new qty so stock can be updated using the trigger

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-30 : 05:45:23

use INSERTED and DELETED tables for that and check for i.ColumnValue <> d.ColumnValue. If you want to handle NULLs also use ISNULL or COALESCE

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

indr4w
Starting Member

27 Posts

Posted - 2013-06-05 : 02:36:29

Thanks Mr. Visakh, its solve.

God bless you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-05 : 02:43:10

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources