Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Sequence Number Trigger

DarkstormDarkstorm Member Posts: 28
Hi!

I would like to know the PL/SQL for generating sequence number on the pre Insert trigger without using the one which Oracle provides.

Thanks a lot

Comments

  • nneomanneoma Member Posts: 61
    : Hi!
    :
    : I would like to know the PL/SQL for generating sequence number on the pre Insert trigger without using the one which Oracle provides.
    :
    : Thanks a lot
    :




    the pre-insert trigger should have this PL/SQL code
    select :sequence.sequence_name
    into :block_name.item_name
    from dual;

    or

    :block_name.item_name := :sequence.sequence_name;


    where sequence_name is the name of an already created sequence
    block_name is name of the block where the item to hold the sequence number resides and
    item_name is the name of the item to hold the sequence number.

    i hope this is what you need.

  • infidelinfidel Member Posts: 2,900
    : Hi!
    :
    : I would like to know the PL/SQL for generating sequence number on the pre Insert trigger without using the one which Oracle provides.

    The only way to get a sequence number without using an Oracle sequence object is to get the MAX() of your sequence field and add 1. This is not safe, however, because multiple users inserting records at the same time can easily generate the same number which immediately defeats the purpose of a sequence-based field.


    [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]

  • olatundeolaolatundeola Member Posts: 42
    [b][red]This message was edited by olatundeola at 2003-2-19 10:17:47[/red][/b][hr]
    [b][red]This message was edited by olatundeola at 2003-2-19 10:15:50[/red][/b][hr]
    Hi
    [code]
    Not at all. Implemeting a sequence generator other than the one oracle
    provices will not help especially when two or more are making
    insertions at the same time(very rare, but 2 out of 100 insertion is
    possible) as said by infidel(I agree with him)

    but if there is, pls let us know we would appreciate it

    [/code]
    [size=5][italic][b][blue][RED]O[/RED]lat[/blue][/italic][/b][/size]







Sign In or Register to comment.