Auto generating Sequences !!

From: chani <chanakam2000_at_gmail.com>
Date: 10 Apr 2006 10:18:21 -0700
Message-ID: <1144689501.151222.118100@j33g2000cwa.googlegroups.com>

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  /* Here I want to generate sequence name
dynamically,
                                                  But have no idea how
to do it, can i use char functioons
                                                  here (need help for
it also) */
        start with 1
        increment by 1
        maxvalue 9999
        minvalue 1
        nocache
        nocycle
        noorder
       END IF;

   END; 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:18:21 CDT