trigger on insert - 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)

trigger on insert

Author

Topic

cristuballa
Starting Member

13 Posts

Posted - 2003-10-01 : 20:49:44

Im just new to sql using trigger and im using sql server 2000. This is how the code should work - Whenever I add record into SalesQuantity_tbl having the product Sold_Quantity field its value should be use to subtract Quantity_Onhand of certain producton Product_tbl. below is my code but it has errors and i dont know how solve it. Please help thanks in advanced.

CREATE TRIGGER Subtract_Product ON [dbo].[SalesQuantity_tbl]
FOR INSERT
AS
UPDATE Product_tbl
SET Quantity_Onhand = Product_tbl.Quantity_Onhand - SalesQuantity_tbl.Sold_Quantity
FROM SalesQuantity_tbl Product_tbl
JOIN inserted SalesQuantity_tbl on Product_tbl.Product_ID = SalesQuantity_tbl

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-10-01 : 23:15:32

First you need to join on two columns right now its 1 column = 1 table (No Way).
Product_tbl.Product_ID = SalesQuantity_tbl

Second giving aliases to existing tables and aliases to tables you aliased (Did I use the word alias enough)?
Why do you want to complicate your life?
Use different names that why there is less chance of screwing up the query.

cristuballa
Starting Member

13 Posts

Posted - 2003-10-01 : 23:27:05

by the way tables Product_tbl and SalesQuantity_tbl are JOINED

correction: lastline, it
"JOIN inserted SalesQuantity_tbl on Product_tbl.Product_ID = SalesQuantity_tbl.Product_ID"

ITS NOT
"JOIN inserted SalesQuantity_tbl on Product_tbl.Product_ID = SalesQuantity_tbl"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources