SQL & PL/SQL

I have a table that has a composite key and one of the key fields is a sequence based number. I want the autonumber to be based on the first field of the key.

For example,

If I perform inserts as follows:

INSERT INTO tb_division (division_grp) VALUES ('A');
INSERT INTO tb_division (division_grp) VALUES ('A');
INSERT INTO tb_division (division_grp) VALUES ('B');
INSERT INTO tb_division (division_grp) VALUES ('B');
INSERT INTO tb_division (division_grp) VALUES ('A');
INSERT INTO tb_division (division_grp) VALUES ('A');
INSERT INTO tb_division (division_grp) VALUES ('B');
INSERT INTO tb_division (division_grp) VALUES ('B');
INSERT INTO tb_division (division_grp) VALUES ('C');

I would like database to be populated as follows:

A, 1
A, 2
B, 1
B, 2
A, 3
A, 4
B, 3
B, 4
C, 1


I want to create a trigger which I believe can do this task. Any advice or guidance on the trigger?


CREATE table tb_division (
division_grp char(4) not null,
division_num char(2) not null,
constraint pk_division primary key (division_grp, division_num)
);

CREATE or REPLACE TRIGGER trg_divisioninsert
BEFORE INSERT ON tb_division
FOR EACH ROW

BEGIN
.
.
.
.

END trg_divisioninsert;
/