Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003

    Unanswered: Some Sequence/Trigger Questions

    Hi all. Working on a sql server to oracle migration of an existing DB (a few acually) and it seems like I'm going to have to use a combo of sequences and triggers to work some kind of 'autonumber' type of function.

    My questions are the following...
    If I have a table that is filled with data already, and I need a column to get populated with the sequence, is the fact that the column is already populated with numbers going to make things difficult? IE. let's say the ID column already has 20,30,40,60, etc..from SQL Server.
    I want to move that table to oracle, and have the sequence continue from where THAT sequence left off....thoughts?

    Also, when tryin to create the sequence with this code, i get the error shown at the bottom...what am I doing incorrectly?

    SQL> create or replace trigger batch_seq
    2 before insert on tbl_owr_batch
    3 for each row
    4 when ( IS NULL)
    5 begin
    6 select tbl_batch_seq
    7 into
    8 from dual;
    9 end;
    10 /
    when ( IS NULL)
    ERROR at line 4:
    ORA-25000: invalid use of bind variable in trigger WHEN clause

    As always, thanks for your help.

  2. #2
    Join Date
    Apr 2003
    Greenville, SC (USA)
    1st... Get the max number of the table you're coping ...
    select max(autonumbered) from tablename;

    Create your sequence with a number greater than the number from above ...
    The Trigger :

    instead of when ( ...
    create trigger ...
    before update of id


  3. #3
    Join Date
    Apr 2004

    Arrow syntax prblem

    in when condition the colon is not required.
    oracle systax.


Posting Permissions

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