forms quick question

[Scunningham99]

Howdy

Quick Q:

I want PK to be populated when a new record is entered using a sequence, but im stuck as to which trigger to use. I have tried using when-new-record-instance which is not good as everytime i go to new block it increments the counter. This is not good as i only wish it to increment when i wish to insert a new record.

I have tried pre-insert and on-insert which also do not work as they error as the PK field (custno) is null when i try and save.
Heres the code in the trigger:-
select customers_custno.nextval
into :CUSTNO --select nextval from sequence into field
from dual;

Im sure this is something simple, but im having a bad day!

any help would be greatly appreciated!




Sy UK

 

[lewisp]

In your PRE-INSERT trigger all you should need is

[tt]:block.custno := customers_custno.nextval;[/tt]

 

[Scunningham99]

thanks for reply...

it will not allow me to compile... it says ":customers.CUSTNO := customers_custno.nextval; not allowed in this context"

also when i try:-

:block.CUSTNO := customers_custno.nextval;

I get bad bind variable ":block.CUSTNO"

any ideas???

Sy UK

 

[lewisp]

My apologies - it was too early this morning!

Your original SQL should be OK:

[tt]SELECT customers_custno.nextval
INTO :customers.CUSTNO
FROM sys.dual;[/tt]

Do you have more than one CUSTNO field in your form?

 

[Scunningham99]

yes heres desc on table... i am entering all fields.. still not working any ideas..

ps thanks for your help! this is very strange!

SQLWKS> desc customers
Column Name Null? Type
------------------------------ -------- ----
CUSTNO NOT NULL NUMBER(6)
FORENAMES NOT NULL VARCHAR2(30)
SURNAME NOT NULL VARCHAR2(20)
ADDRESS NOT NULL VARCHAR2(35)
DATE_OF_BIRTH NOT NULL DATE
LICENCE_NO NOT NULL NUMBER(8)
PENALTY_POINTS NOT NULL NUMBER(1)


Sy UK

 

[BJCooperIT]

:block.CUSTNO" should be ":customers.CUSTNO"

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Systems Project Analyst/Custom Forms & PL/SQL - Oracle/Windows

 

[Scunningham99]

thanks it is:-

SELECT customers_custno.nextval
INTO :customers.CUSTNO
FROM sys.dual;

it would not compile with :block anyway!

Thanks for your help though. this has got to be something simple



Sy UK

 

[Scunningham99]

no key-commit triggers are set.

i have fixed thi by giving the custno column an initial value of 0. when the column is saved the sequence is added. its not perfect but it works... thanks anyway!!



Sy UK