[scoobymib3]

Hi all,

Before I start please let me explain I am not a dba, Im a java developer so I have basic database skills but I am no expert so excuse me if this has been covered before or is an obvious question!

We have an application which is php front end and oracle db back end. This application is a simple data capture but it creates a reference number on the fly which is unique to each row entered. This reference number is a sequencial number based on two other columns with the same table.

There are columns area, year and reference number.
The reference number is a sequencial number which is the number of entrys into the database per area per year so at the moment I have a trigger that works out the number and enters it...

CREATE OR REPLACE
TRIGGER TRIGGER1 BEFORE INSERT ON TBLREPORT 
FOR EACH ROW 
DECLARE
    v_max_ref number;
    v_year number;
BEGIN
    v_year := to_number(to_char(current_date, 'YYYY'));
 
    SELECT nvl(max(ref), 0) INTO v_max_ref
    FROM TBLREPORT 
    WHERE YEAR = v_year
    AND AREA = :new.AREA;
 
    :new.YEAR := v_year;
    :new.REF := v_max_ref + 1;
END;

This works fine but if two people click save at the same time it repeats the reference number so it is not unique.

I need it to be unique but Im not sure about the best way of doing it. Should I implement a lock on the table before the insert??

Thanks in advance for your help, wish I had more time to try and figure it all out for myself!!

Cheers
Martin

[anacedent]

use a SEQUENCE

[scoobymib3]

The best way would be to create a sequence for each year and area?? I thought of that, just imagined there was better ways of doing it...

[anacedent]

Alternatively, place a UNIQUE constraint on the column.
If/when a duplicate occurs, trap the error & increment the value (again).

[scoobymib3]

The thing is none of the columns will be unique, the reference number will have several values the same in it as it would be dependent on the area and year aswell

for instance.....

AREA REF YEAR
AZ 1 08
BZ 1 08
AZ 2 08
FZ 1 08

[beilstwh]

then set the unique index on all three columns.