Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: create trigger after insert

    Hi,
    I'va one table with 2 column:
    Name varchar2(32) primary key
    Code_name number

    When I insert a new value in Name I must create a trigger that insert automatically a progressive number beginning from 10000.

    How can I create this trigger?

    Thanks
    Raf

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: create trigger after insert

    Originally posted by raf
    Hi,
    I'va one table with 2 column:
    Name varchar2(32) primary key
    Code_name number

    When I insert a new value in Name I must create a trigger that insert automatically a progressive number beginning from 10000.

    How can I create this trigger?

    Thanks
    Raf
    Creating the trigger is straightforward:

    CREATE TRIGGER trig BEFORE INSERT ON table
    FOR EACH ROW
    BEGIN
    :NEW.code_name := ?????;
    END;

    The harder part is to get "a progressive number beginning from 10000" - unless you are prepared to accept gaps in your numbers, which really is the best way forward. If you can accept gaps, then create a SEQUENCE:

    CREATE SEQUENCE seq START WITH 10000;

    Then the trigger becomes:

    CREATE TRIGGER trig BEFORE INSERT ON table
    FOR EACH ROW
    BEGIN
    SELECT seq.NEXTVAL INTO :NEW.code_name FROM DUAL;
    END;

    If you insist that your code_names must NOT have gaps, then you have more of a problem. You would have to either (a) SELECT MAX(code_name)+1 FROM table, or (b) create a separate table to hold the latest value. Neither of these approaches is really desirable. Approach (a) will suffer from the "mutating table" issue, i.e. you are trying to select from the table being modified. Both approaches can cause performance problems, because they serialize inserts to the table - if user A has inserted a new row but not committed, no other users can insert a new row, because they can't establish the next available number. In any case, gaps will still appear unless you ban deletes.

    It's best to learn (and educate users) not to expect gap-free numbering for auto-generated IDs, and use a sequence.

Posting Permissions

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