SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Update stock
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

indr4w
Starting Member

Indonesia
27 Posts

Posted - 05/30/2013 :  03:14:22  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 05/30/2013 :  03:49:14  Show Profile  Reply with Quote
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
Go to Top of Page

indr4w
Starting Member

Indonesia
27 Posts

Posted - 05/30/2013 :  04:12:16  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/30/2013 :  04:21:50  Show Profile  Reply with Quote
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
Go to Top of Page

indr4w
Starting Member

Indonesia
27 Posts

Posted - 05/30/2013 :  04:55:26  Show Profile  Reply with Quote
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/30/2013 :  04:57:25  Show Profile  Reply with Quote
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
Go to Top of Page

indr4w
Starting Member

Indonesia
27 Posts

Posted - 05/30/2013 :  05:25:53  Show Profile  Reply with Quote
How do I compare, qty old with the new qty so stock can be updated using the trigger
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/30/2013 :  05:45:23  Show Profile  Reply with Quote
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
Go to Top of Page

indr4w
Starting Member

Indonesia
27 Posts

Posted - 06/05/2013 :  02:36:29  Show Profile  Reply with Quote
Thanks Mr. Visakh, its solve.

God bless you
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/05/2013 :  02:43:10  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000