Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Posts
    5

    Unanswered: trigger a sequence in Oracle?

    I am trying to create a trigger on a table in Oracle that will create a sequence each time a row is inserted into a table. When I try to compile the trigger I get a message that the trigger is invalid.

    The syntax I am using is:

    create or replace trigger on_part_insert
    before insert on PART
    for each row
    begin
    select PART_ID_seq.nextval
    into :new.PART_ID
    from dual;
    end;

    The table definintion is

    create table part(part_id number, part_number char(30))

    Please help!!!!

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

    Re: trigger a sequence in Oracle?

    Originally posted by bernsch
    I am trying to create a trigger on a table in Oracle that will create a sequence each time a row is inserted into a table. When I try to compile the trigger I get a message that the trigger is invalid.

    The syntax I am using is:

    create or replace trigger on_part_insert
    before insert on PART
    for each row
    begin
    select PART_ID_seq.nextval
    into :new.PART_ID
    from dual;
    end;

    The table definintion is

    create table part(part_id number, part_number char(30))

    Please help!!!!
    Use SHOW ERROR to see what is wrong - there's nothing obvious.

  3. #3
    Join Date
    Dec 2002
    Posts
    11

    Re: trigger a sequence in Oracle?

    Assuming the sequence exists with that name there should be no problem! I just set it up on my DB and all compiled.

    Bob

  4. #4
    Join Date
    Dec 2002
    Posts
    1

    Re: trigger a sequence in Oracle?

    SATYA - The following trigger code works fine. Only problem could be you may have not defined the Sequence.
    First create the sequence using the following syntax and then create the trigger.
    create sequence PART_ID_seq start with 1;


    Sequence created.

    SQL> create or replace trigger on_part_insert
    2 before insert on PART
    3 for each row
    4 begin
    5 select PART_ID_seq.nextval
    6 into :new.PART_ID
    7 from dual;
    8 end;
    9 /

    Trigger created.


    Thanks
    Satya

    Originally posted by bernsch
    I am trying to create a trigger on a table in Oracle that will create a sequence each time a row is inserted into a table. When I try to compile the trigger I get a message that the trigger is invalid.

    The syntax I am using is:

    create or replace trigger on_part_insert
    before insert on PART
    for each row
    begin
    select PART_ID_seq.nextval
    into :new.PART_ID
    from dual;
    end;

    The table definintion is

    create table part(part_id number, part_number char(30))

    Please help!!!!

Posting Permissions

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