Re: Auto increment value after insert

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