Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2008
    Posts
    3

    Unanswered: Synchronous Trigger

    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...

    Code:
    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

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    use a SEQUENCE
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2008
    Posts
    3
    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...

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Alternatively, place a UNIQUE constraint on the column.
    If/when a duplicate occurs, trap the error & increment the value (again).
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jul 2008
    Posts
    3
    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

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    then set the unique index on all three columns.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •