Oracle Trigger
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Oracle Trigger

  1. #1
    Join Date
    Oct 2001
    Location
    UK
    Posts
    7

    Exclamation

    Hello,

    I am trying to use a pre_insert trigger with my oracle db:-

    create table book(
    id number,
    name varchar2(100)
    );



    CREATE OR REPLACE TRIGGER book_ins
    BEFORE INSERT ON book FOR EACH ROW
    BEGIN
    SELECT book_seq.NEXTVAL
    INTO :new.id
    from DUAL;
    END;

    Does anyone know if this is correct?

  2. #2
    Join Date
    May 2001
    Location
    Sydney Australia
    Posts
    44
    That looks ok, assuming that you have created the sequence "book_seq" of course.
    Max Hugen
    Hugen Enterprises Pty Ltd
    www.hugen.com.au

  3. #3
    Join Date
    Nov 2001
    Location
    Central U.S.
    Posts
    35
    That is one way to accomplish the task at hand. Another way, which will allow a specific ID to be inserted, would be this:

    CREATE OR REPLACE TRIGGER book_ins
    BEFORE INSERT ON book FOR EACH ROW
    BEGIN
    IF :new.id IS NULL THEN
    SELECT book_seq.NEXTVAL
    INTO :new.id
    from DUAL;
    END IF;
    END;
    /

    If no value is supplied in the insert statement for ID then the sequence will populate the field, otherwise the supplied value will be used in the inserted record.

    David Fitzjarrell
    Oracle Certified DBA
    David D. Fitzjarrell
    Oracle Certified DBA

  4. #4
    Join Date
    May 2001
    Location
    Sydney Australia
    Posts
    44
    David, your suggestion is very helpful where records could be imported from another source, with their own unique ids.

    What would happen if a record with an ID of 500 were imported, but the sequence was only up to, say, 450?
    Max Hugen
    Hugen Enterprises Pty Ltd
    www.hugen.com.au

  5. #5
    Join Date
    Nov 2001
    Location
    Central U.S.
    Posts
    35
    I take care of that with the following, executed immediately after a successful data load:

    rem
    rem Rebuild the BOOK_SEQ sequence
    rem
    declare
    --
    -- Get highest ID value in the table
    --
    cursor get_max_id is
    select max(id)
    from book;

    max_id number:=0;
    sqlstmt varchar2(255);
    begin
    --
    -- Retrieve maximum ID value from BOOK
    --
    open get_max_id;
    fetch get_max_id into max_id;
    close get_max_id;
    --
    -- Add one
    --
    max_id := max_id + 1;
    --
    -- Drop the current BOOK_SEQ
    --
    sqlstmt:='drop sequence book_seq';
    execute immediate sqlstmt;
    --
    -- Create new SEQ_MSG with new starting value
    --
    sqlstmt:='create sequence book_seq start with '||max_id||' increment by 1 maxvalue 999999999999999999999999999 minvalue 1 nocycle noorder cache 20';
    execute immediate sqlstmt;
    end;
    /

    This recreates the sequence so that it will start at the immediately after the highest inserted ID value.
    David D. Fitzjarrell
    Oracle Certified DBA

Posting Permissions

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


Click Here to Expand Forum to Full Width