getting sequence value in trigger

[puneet]

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

[sree_sri]

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
------------------------

[bensr]

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.

[Oracledba8]

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

[dplante]

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;