Re: Trigger pgm to increment seq# in file

Let me try a different example and you tell me if we're thinking the same
thing now.

create table ordhead (
Ordernumber int as identity,
customernumber int,
primary key(ordernumber))
;

insert into ordhead (customernumber) values(2);

Now you're thinking "Hey I need to know that new order number!". So you
do this instead

select ordernumber
from final table (insert into ordhead (customernumber) values(27));

And now you'll back the newly generated order number. In our case, a 2.
Basically you just wrap your insert with this "select ... from final
table" stuff.

Now, let's add a line to this order.

create table ordline (
ordernumber int not null,
orderline int not null,
itemNumber int,
ordqty dec (15,5),
constraint ordline_ordheadFK foreign key (ordernumber) references ordhead
(ordernumber)
);

insert into ordline (
ordernumber,
orderline
itemNumber,
ordqty)
values (
:NumberRetrievedFromSelectFromFinalTable,
1,
4,
2.3);

And, since you could have
ordernumber orderline
1, 1
1, 2
1, 3
2, 1
2, 2
You want to know how you can get this order line sequence to auto
increment?
Good luck with that...





Rob Berendt