Re: Auto generating Sequences !!

chani wrote:
> Thank everyone who helped me.
> I learned lot.
> Instead of my horrible requirement I found a simple solution for my
> requirement.
> sql code are bellow
>
> create or replace trigger t
> before insert
> on loan_payment
> for each row
> DECLARE
> num number(2,0)
> begin
> select count(loan_number)+1 into num from loan where
> loan_number=:new.loan_number ;
> select num into :payment_number from dual;
> end;
>
>
> It will automatically fill it is howmanyth payment for a lon
> when new payment insert. (this is xactly what i wanted)
> (I can generate required format with some modification)
>

Now, now... Seems like a nice idea at first, isn't it? ;) Tsk, tsk... You didn't think of a few things:

1. As it is written, it won't work. It's invalid syntax and invalid logic. I can see what you were thinking though: let's take max(payment_number) for particular loan_number we have at this time and increment it by 1 to get our next payment number. You can't do this in a trigger, because you need to select from the same table the trigger is created on - you will get "table is mutating" error. You will have to put this logic into a procedure and put an exclusive lock on the master row (loan) before you can proceed with insert into payments table (see below why.)
2. Imagine this simple situation:

   clerk 1 starts entering a new payment for loan X. A row    is inserted into the payments table, with max(payment_number)    incremented by 1 as new payment number. The transaction is    NOT yet committed. At about the same time, clerk 2 starts    entering another payment for the same loan X. Another row is    inserted, which receives *the same* payment number, because    the first transaction is not committed yet and second transaction    doesn't see changes it made. Both transactions    are then committed and you end up with inconsistent db:    you have two payments for the same loan with identical    payment numbers.

Hth,

    Vladimir M. Zakharychev
    N-Networks, makers of Dynamic PSP(tm)     http://www.dynamicpsp.com Received on Tue Apr 11 2006 - 12:19:07 CDT