April 13, 2007 at 8:31 am
How can you write a select query for the last record updated or inserted in the table.
Suppose i have Table A and i have a stored procedure which inserts if there is no record and updates if records exists in the Table A.
April 13, 2007 at 8:49 am
you cannot do that natively. the change/insert information related to data is not captured.
usually, if this is required, you'd add two columns to the table, as well as a trigger....search the Scripts section for "Audit" and you'll find a ton of examples. if you have a stored proc doing the inserts and updates already, then you'd add the code to the stored proc to start placing values in the two new columns, that way you could avoid the trigger, but the trigger would potentially capture changes to the inserts/updates that occur outside fo your stored proc.
the two columns are to capture the Created Data of the row with a default of getdate();, and a LastUpdated column that is updated by the trigger.
like i said, the script section has a number of examples and solutions for this, and you just have to determine which tables must have an audit trail.
other than that, you can infer the last row inserted based on the identity column of the table (if it has one), and you could only find updates by using a log trolling program from a third party vendor. that only works if you have a database set to FULL and not SIMPLE
Lowell
April 13, 2007 at 10:08 am
Add a timestamp column to the table
and use
SELECT TOP 1 * FROM ORDER BY [timestampcolumn] DESC
to find the last row inserted or updated
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply