Bloody joins! - 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

General SQL Server Forums

New to SQL Server Programming

Bloody joins!

Author

Topic

nick_dkc
Starting Member

25 Posts

Posted - 2009-12-17 : 09:07:10

I'm trying to run a very simple update on a table - after each stock transaction is inserted into a transaction table - on a very simple stock (inventory) table, with nothing complicated at all. But I guess because I don't do enough SQL stuff all of the time I simply can't get my head around the necessary joining.

Here's the base 'stock' table.
--------------------------
CREATE TABLE dbo.stock
(
stockID int,
stockDescription nvarchar(50),
stockNew int,
stockUsed
)
--------------------------

and to populate the table:
--------------------------
INSERT INTO dbo.stock VALUES (1 , 'shirt', 5, 0)
INSERT INTO dbo.stock VALUES (2 , 'jacket', 10, 5)
INSERT INTO dbo.stock VALUES (3 , 'shorts', 5, 10)
INSERT INTO dbo.stock VALUES (4 , 'trousers', 15, 5)
--------------------------

And here's the transaction table - which we need to see what was issued to or received by who each time:
--------------------------
CREATE TABLE dbo.stockTransactions
(
transactionID int,
stockID int,
newIssued int,
newReceived int,
usedIssued int,
usedReceived
)
--------------------------

My application posts the inserts to the transactions table fine. I have tried to write an 'after insert' trigger that will simple do the calculations and update the values in the stock table.

Here's my trigger:
--------------------------
CREATE TRIGGER [dbo].[tr_InsertStockTransaction] ON [dbo].[stockTransactions]
AFTER INSERT AS
BEGIN
SET NOCOUNT ON

UPDATE dbo.stock
SET
stockNew = stockNew - (dbo.stockTransactions.newIssued + dbo.stockTransactions.newReceived),
stockUsed = stockUsed - (dbo.stockTransactions.usedIssued + dbo.stockTransactions.usedReceived)

FROM dbo.stock LEFT OUTER JOIN dbo.stockTransactions
ON dbo.stock.stockID = dbo.stockTransactions.stockID

END
--------------------------

However, as you will see if you run this insert

INSERT INTO dbo.stockTransactions VALUES (1, 1, 2, 0, 0, 0)

The results are crazy.

If anyone can point me in the right direction or suggest a better / more effective method of updating my stock table I'd be very grateful.

BTW, here's a few more transactions in case someone needs them for testing:

INSERT INTO dbo.stockTransactions VALUES (2, 2, 5, 0, 0, 5)
INSERT INTO dbo.stockTransactions VALUES (3, 3, 0, 10, 5, 0)
INSERT INTO dbo.stockTransactions VALUES (4, 4, 10, 0, 0, 5)

Many thanks

Nick in Dubai

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-17 : 09:36:18

The trigger code should be


CREATE TRIGGER [dbo].[tr_InsertStockTransaction] ON [dbo].[stockTransactions]
AFTER INSERT AS 
BEGIN
SET NOCOUNT ON

UPDATE dbo.stock
SET
stockNew = stockNew - (i.newIssued + i.newReceived),
stockUsed = stockUsed - (i.usedIssued + i.usedReceived)

FROM dbo.stock inner JOIN inserted as i
ON dbo.stock.stockID = i.stockID

END
go

Madhivanan

Failing to plan is Planning to fail