How to get last row id removed from SQL table

Question

I am trying to get the last Id using the max (Id) function, where Id is the primary key. My code works well if I do not delete the last row from the table, because when I delete the last row, this identifier is saved and in this case max (Id) will extract the last row identifier, but not the identifier that is deleted from the table.

Is there any function that retrieves the remote id of the last line. I saw several posts using functions scope_identity()and @@identityto get the current identifier. I used these functions, but they do not work. I am editing my code inside a dll file, maybe this is a problem.

Answer 1

If you are using SQL 2005 or higher, you can use the OUTPUT clause to get the identifier of the deleted row.

Check out this link for a good example.

Answer 2

You can create a TRIGGER FOR DELETE event and insert deleted records into the log table.

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

You can then query the myLogtable table for deleted records.

Select * FROM Logtable 

Answer 3

@@Identity Scope_Identity() .

, - ( ), Ident_Current

BOL : IDENT_CURRENT , ,

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