how to get the Id of last row deleted from SQL table

Question

I am trying to get the last Id using max(Id) function where Id is the primary key. My code works well unless I delete last row from the table, because when I delete last row then that Id is still preserved and in that case max(Id) will retrieve the last row Id but not that Id which is deleted from the table.

Is there any function which retrieve the last row deleted Id. I have seen some posts using scope_identity() and @@identity functions to get the current Id. I have used these functions but they are not working. I am editing my code inside a dll file may be dat is creating problem.

Answer 1

You can create a TRIGGER FOR DELETE Event, and insert in a log table the deleted records.

CREATE TABLE LogDeletetable 
(
    ID          int           IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    [datedelete] smalldatetime NOT NULL DEFAULT Getdate(),
    ExtID int
 )

CREATE TRIGGER TriggerONDELETE
ON MyTable
FOR DELETE
AS
DECLARE @ID int    
SET @ID = (SELECT ID FROM deleted)    
INSERT Logtable (ExtID ) VALUES(@ID)
GO

Then you can query against the table myLogtable for the deleted records.

Select * FROM Logtable

Answer 2

If you are using SQL 2005 or above you can use OUTPUT clause to get the id for deleted row.

Check out this link for a nice example

Answer 3

@@Identity and Scope_Identity() only return the most recent identity value for the current session.

To get the highest identity value that has ever been assigned to a table (even when some of them have been deleted) you can use Ident_Current

BOL describes it as follows: IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope

http://msdn.microsoft.com/en-us/library/ms175098.aspx