Re: Auto generating Sequences !!

chani wrote:
> Hi,
>
> Thank everyone who helped on my previous post.
> But I have to seek some more help from you.
>
> 1. Can I create a sequence inside a trigger?
> =========================================
> I have two tables
> loan(loan_number,....)
> loan_payment(loan_number,payment_number,...) /*This is a weak
> set depend on loan*/
>
> I want to create sequences for each entity entered to loantable
>
> My requirement is to have
>
> payment_001,payment_002,... for loan_0001
> payment_001,payment_002,... for loan_0002
> payment_001,payment_002,... for loan_0003 .. & so on
>
> (I want to generate payment_number belong to a loan from sequence)
>
> for this I should have seperate sequence for each loan entry
> such as
>
> seq_loan001,
> seq_loan002,....
>
> I tried following sql trigger, But It says error
>
> CREATE OR REPLACE TRIGGER LOAN_PAY_TR
> BEFORE INSERT
> ON loan
> FOR EACH ROW
> BEGIN
> IF inserting THEN
> CREATE SEQUENCE LP *** please see bottom ***
> start with 1
> increment by 1
> maxvalue 9999
> minvalue 1
> nocache
> nocycle
> noorder
> END IF;
> END;
>
> **** Here I want to generate sequence name dynamically
> (in the format of seq_loan001,...) But have no idea how to do it,
> can i use char functions here (need help for it also) */
>
>
> I tried to find answer in google (Searched for 'auto sequences',
> 'Sequences in trigger' & some more clauses)
>
> I explained my problem in large text. Because I want you to understand
> my question.
> But I don't know how to explain some of them in english properly. I
> hope you will understand.
>
> Can you Please help me. (If this is not possible let me know it!)

You have a requirements problem. Also, see the Caution at http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14220/schema.htm (don't use Oracle sequences if you must never lose sequence numbers).

What you probably want to do is have a parent table with the loan header information in it, and a detail table with the payment history, with a foreign key relationship between them. In your design normalization, you need to specify the attributes of each, perhaps relating customers, loans and payments. This is real basic stuff, asking how to do it with sequences indicates a fundamental issue of understanding. It would be more reasonable to have a counter as part of a primary key, just get the max for the loan and add one to it when you get a payment. If you have multiple data entry people (or processes) getting multiple payments at the same time for the same loan, you perhaps _want_ to have locking issues.

jg

--
@home.com is bogus.
http://bmworacleracing.com/en/4_bor/45_highlights/451_pictures/45113_season_2006/45113_season_2006_flash.4ml?track.refer=/en/4_bor/4_home/4_home.4ml&track.type=cb