Re: Sequences?

Careful, you'll have people thinking that sequences aren't perfect for every need, or even daring to imagine that other platforms have a better solution for 99.9% of uses of sequence (almost invariably it'll be being used to supply an identity)

My solution, which may not work for you, is to change your "when" clause to
 when (new.ID is NULL or new.ID=0)

Then you can explicitly pass a zero in whenever you want a generated value. As it happens this was an easy alteration to make to my app, which handles all inserts through a single generic helper function. Its old implementation was to automatically not supply values for identity columns in most cases. Its "oracle flag" implementation is to pass 0 to identity columns. I guess if you have a app that you are trying to port from another platform which has *lots* of hard-coded inserts then this problem is going to be a pig for you to solve.

Regards

> I'm not concerned with the number of a keystrokes but rather the fact
> that IDENTITY column implemented in the following way
> CREATE SEQUENCE "GB"."ANSWERS_TB_SEQUENCE";
>
> CREATE TRIGGER "GB"."ANSWERS_TB_TRIGGER" before insert on
> "GB"."ANSWERS_TB"
> for each row
> when (new.ID is NULL)
> begin
> select "GB"."ANSWERS_TB_SEQUENCE".nextval into :new.ID from dual;
> end;
>
> can not be NOT NULL and therefore can not be PRIMARY KEY.
> What a nonsence !!!
>
> If I remove when (new.ID is NULL) as somebody suggested I get
> ERROR at line 1:
> ORA-00001: unique constraint (GB.SYS_C001584) violated
> meaning that it also can't be UNIQUE.
>
> All in all inmplementing idintity on Oracle is terrible pain in the
> ass !
>
> BTW, any suggestions how to solve the above problems ?
>
Received on Tue Feb 26 2002 - 07:34:49 CST