| Adding sequence to form. [message #142765] |
Mon, 17 October 2005 11:00  |
Sharaf
Messages: 62
Registered: September 2005
Location: U.K
|
Member |
|
|
please i have a sequence which i created in sql plus to increase my primary key.
the sequence is as shown below.
it works fine when insertng valus in sql but in oracle form, how do i attach it to my primary key columns.
CREATE SEQUENCE loan_id_seq
CREATE OR REPLACE TRIGGER loan_id_trigger
BEFORE INSERT ON loan FOR EACH ROW
BEGIN
SELECT 'L' || TO_CHAR(loan_id_seq.NEXTVAL,'fm000000000')
INTO :new.loan_id
FROM DUAL;
END loan_id_trigger;
Thanks
|
|
|
|
| Re: Adding sequence to form. [message #142817 is a reply to message #142765] |
Mon, 17 October 2005 18:55  |
 |
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
|
Senior Member
Account Moderator |
|
|
You don't HAVE to attach the sequence, depending on what else you do with this value in the rest of your form.
However, if you have defined the field as mandatory or a primary key field in your form then you should consider using the sequence in a When-Create-Record trigger at the block level and populate your primary key there. Also in your trigger ALWAYS test to make sure the key field ('loan_id') is NULL before populating it.
What you MAY get, however, is 'holes' in your load sequence numbering. If you MUST HAVE an absolute ascending contiguous sequence then consider creating your number MANUALLY using MAX(loan_id)+1 (you will have to strip the 'L' prefix, of course).
David
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
