liquibase - how to rollback an insert change set that is using sequence to populate id field

Question

I am trying to use liquibase to perform db changes for each deployed version (I'm using oracle db).

When I insert new data to a table, I'm using sequence to populate the ID field. But it is also important to me to have the abilty to rollback this insert - meaning delete the newly created row with the id create from the next value of the sequnece.

My question is how to write a rollback to the changeSet that will delete the new row using the created id from the sequence. (I can't use the sequence itself because its value can change many times before perfroming the rollback)

For example:

{
    "changeSet": {
        "id": 1,
        "author": "somebody",
        "changes": [
            {
               "insert": {
                   "tableName": "EMPLOYEES".
                   "columns": [
                      {
                         "column": {
                             "name": "id",
                             "valueSequenceNext": "EMPLOYEES_SEQ"
                         }
                      },
                      {
                         "column": {
                             "name": "name",
                             "value": "john dou"
                         }
                      }
                   ]
               }
            }
        ],
        "rollback": "here rollback the insert using the sequence"
    }
}

Answer 1

If it's possible that you created many new inserts using that sequence, before performing rollback, then it's not possible to rollback it automatically.

Quick, not ideal workaround:

• what about creating additional TEMP_TABLE table to store this created_id?
• columns: CREATED_ID, CHANGESET_ID, ORDINAL_NUMBER
• write a rollback to DELETE row from TEMP_TABLE where ORDINAL_NUMBER is MAX(ORDINAL_NUMBER)?

Can't see any other options, Liquibase itself doesn't store any info about inserted records.