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.
| Author |
Topic |
|
timothymannah
Starting Member
14 Posts |
 Posted - 2005-11-07 : 22:35:02
|
| Is there a function that can pick up the identity of the last updated record in the same way the @@IDENTITY picks up the last inserted record? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-11-07 : 22:53:29
|
| The only non-trigger method I can think of is to add a timestamp (NOT datetime) column to your table, then use this query:SELECT identitycol FROM myTable WHERE timestampColumn=@@DBTSThe downside to this technique is that if you have multiple tables, you'd have to query each one, and you can't distinguish between a newly inserted row or one that was updated. Also, UPDATEs that affect multiple rows will only return one of them (never assume that you will always UPDATE only one row)The best way to keep track of most recently updated rows is to use a trigger. |
 |
|
|
timothymannah
Starting Member
14 Posts |
Posted - 2005-11-08 : 01:10:48
|
| how do triggers work in this regard? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-08 : 01:23:55
|
| "how do triggers work in this regard?"You could have a column in your table (called UpdateDate or somesuch) that is updated by a trigger.I posted some example code here:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=57227Or you could have a trigger "save" all updated records into an Audit table - thus you would have a history of changes.Either way, consider what you want to have happen when you bulk import records - we generally want to preserve the UpdateDate of a record when we import it from another database, not give it today's date. So we use stored procedures (i.e. when the records is created or updated) to set the UpdateDate, rather than triggers.Kristen |
|
|
|
|
|
|
Finding the IDENTITY of the last updated record