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

General Discussion
1

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"
    }
}
2

Hi, maybe with an alter sequence change which reset the minValue ?

3

Oracle does have the RETURNING clause for DML to get the value from the sequence:

INSERT INTO t1 (id, name)
VALUES (employees_seq.nextval, ‘john dou’)
RETURNING id INTO v_id;

But that is really for use in PL/SQL, and I can’t think of a good way to use that in a subsequent rollback. Maybe somebody else has a good idea.

4

What do you mean? How can it help my situation?

5

Sorry, I read too quickly. I thought the sequence should be reset to its previous value. :smiling_face:

It’s actually a tricky rollback case …