Ask the Expert

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 table.

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: