Hello Tom,
I have one table Invoice which has 6 columns.Out of which 2 column are composite primary key.
create table invoice (
area integer,
invoid number
generated always as identity,
designation varchar (20),
date_inv date,
discount_tot number (5,2),
cost_tot number (12,2)
);
alter table invoice
add constraint t_pk
primary key ( Area, Invoid ); So, my question is
1) How can generate a sequence (invoid) for each region?
For example:
Area 1200 use invoid from 1 to 1500
Area 1500 use invoid from 1501 to 4000
Area 2000 use invoid from 4001 to 5000
Area 2500 use invoid from 5001 to 5750, etc
All the Users use the same table (Invoice), but users in Area 1200 when insert a record the program has to use the own Area sequence (invoid)
I need to generate
Thank you in advance.
Alberto Rico
If you want to do something like this, you can:
- Create a sequence for each area
- Have a trigger which inspects the value for area and chooses the appropriate sequence.
For example:
create table invoice (
area integer,
invoid number,
designation varchar (20),
date_inv date,
discount_tot number (5,2),
cost_tot number (12,2)
);
alter table invoice
add constraint t_pk
primary key ( Area, Invoid );
create sequence area1_seq
start with 1
maxvalue 1500;
create sequence area2_seq
start with 1501
maxvalue 4000;
create or replace trigger invoice_trg
before insert on invoice
for each row
begin
if :new.area = 1 then
:new.invoid := area1_seq.nextval;
elsif :new.area = 2 then
:new.invoid := area2_seq.nextval;
--etc.
end if;
end;
/
insert into invoice ( area )
values ( 1 );
insert into invoice ( area )
values ( 2 );
select * from invoice;
AREA INVOID DESIGNATION DATE_INV DISCOUNT_TOT COST_TOT
1 1 <null> <null> <null> <null>
2 1501 <null> <null> <null> <null> But... THIS IS A TERRIBLE IDEA!
Are you really saying there's an upper limit of 1,500 invoices for area 1200?! Seriously?
Because that's what making ( area, invoice_id ) the primary key and setting the limit for area 1200 to the values 1 - 1,500 means.
I strongly urge you to reconsider this design. Make the primary key a simple sequence/identity column.
If you need to make a business key which is a combination of area + invoice#, put a unique constraint on these columns. And come up with a scheme which won't run out of numbers!