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

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top