Retrieve Identity After Delete

[jalbao]

In my db I have a couple of tables that look similar to the following:

Table1
fooID -> Primary Key Identity
barID -> Foreign Key of Table2

Table2
barID -> Primary Key Identity

If I were to run a DELETE statement on Table1 as in:

DELETE FROM Table1 WHERE barID = 33

Is there a sql function or someway to retrieve which "fooID" 's were deleted from Table1?

It's like I need something that does the opposite of the @@IDENTITY function. Instead of retrieving a newly added identity on an INSERT, I need to get the identities that have been removed from the table.

 

[RickSpr]

You would need to retrieve the fooIDs prior to executing the DELETE:
SELECT fooID FROM Table1
WHERE barID = 33
This is not foolproof. If another user inserted or deleted rows in Table1, or edited a barID, after the SELECT and before the DELETE, the query results could become inaccurate.

If you were doing the deletions in a form (perhaps in Datasheet View) based on a join between the tables, you could save the actual fooIDs in the Delete event. However, such a form would be showing records from Table1, where barIDs occur multiple times; that's probably not what you wanted.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[lespaul]

I think you would have to run a query to select all the FooId's that were going to be deleted and store them in a temp table in order for you to be able to see which ones were deleted.


Leslie

 

[JonFer]

You can find the barID values that are in table2 and not table1 but that shows you everything that was deleted, not what was JUST deleted.

You can append the records to a "temp" table before deleting them.

You can change the delete to an update and set a flag that the record is no longer active.

You can process the deletes in a VBA function and track the deleted records that way. This is usually slower than a SQL delete.



John

http://www.interautomateconsulting.com

 

[PHV]

Seems you're playing with SQL server, so just use triggers.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244