Q

Retrieve last row inserted into table in Oracle

How can I retrieve the last row inserted into a table? I don't want to have to specify the row number and I can't change the structure of the table.

How can I retrieve the last row inserted into a table? I don't want to have to specify the row number and I can't change the structure of table.

In relational database theory, there is no concept of the "first" or "last" row of a table. And using ROWNUM wouldn't help here either as you are not guaranteed any specific ordering in the table.

Most people who have this requirement add a column to the table of DATE datatype (or maybe TIMESTAMP) and then create a trigger to update this column's value with the current date and time on INSERT. If you can't modify this table's structure, then you can create another table consisting of this table's primary key column(s) and the column to show when that PK value was inserted into the original table. Then create a trigger to populate your newly created table when a row is inserted into the original table.

This was first published in May 2007
This Content Component encountered an error

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close