how to find out id number from last updated row? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

how to find out id number from last updated row?

Author

Topic

adamgayo
Starting Member

2 Posts

Posted - 2010-12-30 : 13:34:53

how to find out id number from last updated row? this my code:

ALTER trigger [dbo].[updateTotalPayment]
on [dbo].[payment]
for update as
if update(totalpayment)

begin

declare @balance float
declare @totalpayment float

set @balance = (select balance from payment where id =)
set @totalpayment = (select totalpayment from payment id =)

update balance
set balance = (@balance - @totalpayment)

end

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-12-30 : 13:41:18

Use SCOPE_IDENTITY()

Jim

Everyday I learn something that somebody else already knew

Kristen
Test

22859 Posts

Posted - 2010-12-30 : 13:51:03

It looks like you are assuming that the Trigger will only process a single row - that isn't the case, it will fire once regardless of how many rows are actually updated.

There are two "pseudo" tables : "deleted" and "inserted" that contain the Before/After data for the rows which are included in the UPDATE statement - so you can do:


UPDATE U
set balance = (I.balance - I.totalpayment)
FROM balance AS U
    JOIN inserted AS I
        ON I.MyID = U.MyID
    JOIN deleted AS D
        ON D.MyID = U.MyID

you may need to take into account the DELETED table's previous values too - I'm guessing, but maybe:


set balance = (I.balance - D.balance) - (I.totalpayment - D.totalpayment)

or similar

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources