Re: Autonumber trigger

Frank wrote:

> Hi!
>
> I'm curious/discussing with myself :-)))
> Given that you whish to add a trigger in the way you describe:
>
> If you allow INSERT statements to enter a primary key
> that has been generated "in some way" decided by the calling logic (your:
> insert into Test values (100, 'Test');)
> you have to code some workaround for the situation when _another_ calling
> logic INSERT's and
> and uses the TRIGGER defaulting and the sequence picks the value 100. You'll
> get a primary key problem then.
> Either you must catch the exception and do "something" to clean up,
> or make sure that the next available number from the sequence is 101...
> (loop :-(( to pick up all intermediate numbers?)
>
> My question is, what is the reason for allowing both: Add trigger to compute
> the PK, and allowing the developer to enter a "home made" PK?
>
> If the problem is that there is a mixture of old and new PK solutions,
> perhaps the snippet;
> IF :NEW.TestID IS NOT NULL
> THEN
> RAISE_APPLICATION......
> END IF;
>
> in the triggers could help if the appliaction was sent through a Test cycle?
>
> I don't want this to be critisism of the approach, I just don't understand
> what problem this solves, or what advances you get, and would be interested
> in knowing it.
>
> Frank

It solves the problem presented.

Of course you can potentially have key violations. That is what the EXCEPTION part of a PL/SQL block is for.

The basic concept of what is being attempted here is fraught with the possibility of key violations.

Daniel A. Morgan Received on Thu May 17 2001 - 00:09:32 CDT