Re: Autonumber trigger

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 Received on Wed May 16 2001 - 11:03:37 CDT