Re: Auto increment value after insert

[x y <xy6581@xxxxxxxxx>]

Assuming you don't/can't use DDL to add an identity column or some other
SQL-ness, use a very simple SQL "before insert" trigger.  Your regular
(non-SQL) program inserts the row with gpdline = 0 and this trigger fires
and does the heavy lifting.

CREATE OR REPLACE TRIGGER GPP020_Insert_Before
BEFORE INSERT ON gpp020
REFERENCING NEW AS new_row FOR EACH ROW MODE DB2ROW
PROGRAM NAME GPP020INSB
WHEN (new_row.gpdline = 0)
BEGIN
 SELECT COALESCE(MAX(gpdline),0) INTO new_row.gpdline FROM gpp020
 where gpdco   = new_row.gpdco
   and gpddiv  = new_row.gpddiv
   and gpdcust = new_row.gpdcust

   and gpdeff  = new_row.gpdeff;
SET new_row.gpdline  = (new_row.gpdline+1),
    new_row.gpdats   = CURRENT_TIMESTAMP,
    new_row.gpdauser = CURRENT_USER,
    new_row.gpdcts   = CURRENT_TIMESTAMP,
    new_row.gpdcuser = CURRENT_USER;

END

On Thu, Jan 13, 2022 at 9:32 AM <smith5646midrange@xxxxxxxxx> wrote:

> I have a header table and a detail table.  The header table has an auto
> increment field for its key that is used to join the detail table.  If I
> have multiple users updating the header table at the same time, how do I
> know the value on the header record that was just inserted so it can used
> when inserting the detail records?  I have been googling and it looks like
> the only way to find it is to use transactions and commit logic (and maybe
> the method I'm finding only works on MySQL and other non-DB2 platforms).
> Is
> there a way other than using transactions and commit logic?
>
>
>
> While the program that I am writing is RPG and I considered posting this to
> that forum, I'm asking this in the technical forum because I assume other
> high level languages have the same problem (like windows programs using
> ODBC
> connections).
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: https://amazon.midrange.com