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

Thread: getting sequence value in trigger

  1. #1
    Join Date
    Nov 1999
    Posts
    226
    Hi
    I have created the following trigger , It is created without any problems. But when I insert a row in tha table , I get "Cnnot insert null vale in column "
    Plase let me know whats wrong or whats missing

    CREATE OR REPLACE TRIGGER RF_PROD_BOM_INS_TRG
    BEFORE INSERT ON RF_PRODUCT_BOM
    FOR EACH ROW
    BEGIN
    UPDATE RF_PRODUCT_BOM SET PBRFNBR = (SELECT F_PROD_BOM_SEQ.NEXTVAL FROM DUAL );
    END;
    /

    regards

  2. #2
    Join Date
    Feb 2001
    Posts
    203
    Try to select current value first. Sequences are act very silly. First if you select current value then only it will give you next value.

    Just on my knowledge
    ------------------------
    sree

  3. #3
    Join Date
    Feb 2001
    Posts
    180
    Shouldn't it just be:
    SELECT F_PROD_BOM_SEQ.NEXTVAL
    into :new.PBRFNBR;
    FROM DUAL;

    You are trying to updata something that isn't there yet.
    Regards
    Ben de Boer

  4. #4
    Join Date
    Feb 2001
    Posts
    184
    Hi,

    Here is what you need to do. Make sure that you don't have any other column defined as Not Null. Your Logic is against the Reality. Before Insert you are Updating the Column, while the purpose of the triiger is to have the Sequence value placed there. Right

    Here is the Triger Code

    CREATE OR REPLACE TRIGGER RF_PROD_BOM_INS_TRG
    BEFORE INSERT ON RF_PRODUCT_BOM
    FOR EACH ROW
    Declare
    V_SeqVal Number;
    BEGIN
    Select F_PROD_BOM_SEQ.NEXTVAL Into V_SeqVal From Dual;
    :New.PBRFNBR = V_SeqVal;
    END;

    Hope this will help you.. Thanks

  5. #5
    Join Date
    Nov 2002
    Location
    Martinez, CA
    Posts
    1
    Just a comment about this query:
    CREATE OR REPLACE TRIGGER RF_PROD_BOM_INS_TRG
    BEFORE INSERT ON RF_PRODUCT_BOM
    FOR EACH ROW
    Declare
    V_SeqVal Number;
    BEGIN
    Select F_PROD_BOM_SEQ.NEXTVAL Into V_SeqVal From Dual;
    :New.PBRFNBR = V_SeqVal;
    END;

    Doing a similar thing in Oracle 9i, I noticed that
    :New.PBRFNBR = V_SeqVal;
    should be
    :New.PBRFNBR := V_SeqVal;

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