Please help for Trigger - 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 2008 Forums

Transact-SQL (2008)

Please help for Trigger

Author

Topic

Lora Brown
Starting Member

2 Posts

Posted - 2011-12-13 : 13:39:22

Suppose I have 2 tables. Store(Table1) has a column ItemQty and Sales(Table2) has a column QtySold. When data inserted in Qtysold column of Sales table, the ItemQty column of Store Table will be updated with a value which is =(ItemQty - QtySold) Now I want to create a trigger which will update this new data in Store table.

Note :- 1. The ItemId(PK) in Store table is ItemId(FK)in Sales table.
2.QtySold should always less than ItemQty.

Please help soon.
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 13:44:01

[code]
CREATE TRIGGER StockUpd
ON Sales
FOR INSERT
AS
BEGIN
UPDATE s
SET s.ItemQty = s.ItemQty -i.QtySold
FROM Store s
INNER JOIN (SELECT ItemId,SUM(QtySold) AS QtySold
FROM INSERTED
GROUP BY ItemId)i
ON i.ItemId = s.ItemId
END
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunnyleoneo
Starting Member

19 Posts

Posted - 2011-12-13 : 14:19:36

You can create after insert on your 2nd table. In that you can write update to your first table by joining with inserted table of sql server. Following is pseudo code. please check is there any syntax error. Hope this will work.

CREATE TRIGGER trigname ON Sales
AFTER INSERT
AS

UPDATE store set itemqty = itemqty - inserted.qtysold
inner join inserted on table1.itemid = inserted.itemid

GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 23:38:52

quote:
Originally posted by sunnyleoneo

You can create after insert on your 2nd table. In that you can write update to your first table by joining with inserted table of sql server. Following is pseudo code. please check is there any syntax error. Hope this will work.

CREATE TRIGGER trigname ON Sales
AFTER INSERT
AS

UPDATE store set itemqty = itemqty - inserted.qtysold
inner join inserted on table1.itemid = inserted.itemid

GO

please check the code before posting
the above is not syntactically correct

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2011-12-14 : 09:38:14

I think trigger should allow for UPDATE??

Lora Brown
Starting Member

2 Posts

Posted - 2011-12-15 : 12:10:35

Thanks a lot Vishkha....It works perfectly for me. Thank you again, appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-15 : 12:13:35

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources