stored proc INSERTED question.

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

stored proc INSERTED question.

Author

Topic

FriendOfGhost
Starting Member

9 Posts

Posted - 2010-09-22 : 11:29:40

Hi friends,
I know this is as basic question but I really need to be sure:

I made a simple stored procedure lets say for update. when update happens, my sp change some data in another table.

my question is, I use (select Amount from inserted) statement in my sp. if 100 people changes data (at exactly same time), does INSERTED table contains only one row ? or should I use it in another way.

another expression:
if I run my sp on my development computer, should I worried about multiple user environment (maybe web server) and heavy workload ? in sp, does INSERTED table always contain 1 row ?

does the code below works all the time or should I change INSERTED usage somehow ?

BEGIN
DECLARE @Amount FLOAT
SELECT @Amount =(SELECT Amount FROM INSERTED)

UPDATE TotalSales set TotalAmount = TotalAmount + @Amount
WHERE ProductID = INSERTED.ProductID
END

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-23 : 12:47:04

first of all what you're taking about is trigger rather than a procedure
INSERTED can contain more than one record based on the update statement . all records which took part in update will have details in INSERTED/DELETED tables. so your method of taking values in variables as above wont work. you should be using joins instead like


UPDATE t
set t.TotalAmount = t.TotalAmount + i.Amount
FROM TotalSales  t
INNER JOIN INSERTED i
ON i.ProductID = t.ProductID

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources