I'm connecting to a legacy database that is our ERP system. I have a table that is using composite keys and one of those fields is incremented but it is unique within a subgroup of records. I have no ability to change this although I wish I could.
The typical insert of this MULTI-USER application looks like this:
INSERT INTO MYTABLE(CustomerId_Field, SequenceID_Field)
VALUES (?CustomerId_Field, SELECT MAX(SequenceID_Field) + 1 WHERE CustomerId_Field = ?CustomerId_Field);
It is my understanding that using increment with key generation will accomplish something like this but it seems to me that increment is only geared for incrementing one column that is unique over an entire table. What is the best way to handle my situation and how?
You need to generate the key yourself, and give it to NH.