Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2010
    Posts
    2

    Unanswered: Get ids from affected rows

    Hello everybody,

    For the research project I am working on, I need to know which rows are affected by a sql-query. Currently I am developing a small framework, able to create select statement retrieving the id of the rows that have been affected by the processed query. I am using a sql-parser to create the corresponding query on the fly. For simple query this works fine, but the creation of corresponding select statements for complex queries is quite difficult.
    For this reason I like to ask you if there is maybe an easier way to retrieve the id (rowid) of the rows that has been affected by the last query.

    Thanks for you help,

    Best regards Andreas

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    What exactly do you mean with "affected"?

    Rows that are returned by the statement?
    Rows that were updated?
    Rows that were deleted?
    Rows that were inserted?
    Rows that were read but not returned?

  3. #3
    Join Date
    Dec 2010
    Posts
    2
    I like to get the id of all rows that have been affected by an select, insert, update or delete query:

    Rows that are returned by the statement?
    Rows that were updated?
    Rows that were deleted?
    Rows that were inserted?

  4. #4
    Join Date
    Jan 2011
    Location
    Bengaluru, India
    Posts
    4

    Rows affected by a query

    Returning rows "touched" by the select is tough. For the insert, update and delete you can set up triggers on the table being affected and either populate the rowids into a temp table (for insert and update) or copy the entire row into another table in case of a delete (since the rowid will be gone.)

    Oracle does have a select trigger, but as far as I know it works on the entire Select statement and not row by row.

    Also I am not sure how many tables you are touching, if it is a join, etc.

    ---------------------------------------------------------
    Bharat
    DB Guru
    Boltell Travel Guides, Audio Guides, Audio Visual and Mobile Tour Guides

  5. #5
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    For INSERT, UPDATE and DELETE you can use the "RETURNING" clause to get the affected ids. But I doubt that this would e.g. include rows deleted/updated because of cascaded constraints.

    For select statements I think it's impossible, because there is no way to find out which rows where e.g. discarded due to a WHERE condition or which rows where used in a subselect.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •