Auto generating Sequences !!

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;

• 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!) Received on Mon Apr 10 2006 - 12:21:44 CDT