Is it possible to use sequence generator in this situation?

Question

I have below table structure

ITEM
| ID(Auto Inc)    | ORG_ID(FK to ORG)    | ITEM_ID               |
|-----------------|----------------------|-----------------------|
| 1               | 1                    | 1 (Initial Val for A) |
| 1               | 2                    | 1 (Initial Val for B) |
| 1               | 1                    | 2 (Incremented for A) |


ORG
| ID   | NAME      |
|------|-----------|
| 1    | A         |
| 2    | B         |

Is there any possibility of using any generator to manage item_id column. This is not id column for ITEM table. Business requirement is to manage item_id sequential for each org.

@JaydeepPatel Maybe [this](https://stackoverflow.com/questions/60198528/is-it-possible-to-have-autoincrement-number-without-it-being-an-id) will be helpful. — SternK, Apr 15 '21 at 11:37
@SternK It is not simple it generator. I need generator based on value of ORG_ID. Look at the my example above. — Jaydeep Patel, Apr 15 '21 at 12:02
@Akina Before looking at trigger I would like to see if this is possible with JPA — Jaydeep Patel, Apr 15 '21 at 12:04

score 0 · Answer 1 · answered Apr 15 '21 at 12:11

You may try to insert using the next query:

INSERT INTO item (org_id, item_id)
SELECT @org_id, COALESCE((SELECT 1 + MAX(item_id)
                          FROM item
                          WHERE org_id = @org_id), 1)

where @org_id is the value to be inserted.

The problem: it may insert duplicates while concurrent insertions occures.

Is it possible to use sequence generator in this situation?

1 Answers1