Subject Re: [ib-support] auto generated ID, default values???
Author Lucas Franzen
zifnabbe@... schrieb:
> Hi,
> I don't get it anymore, I'm trying Firebird 1 beta 2 (on Windows),
> however I've some problems...
> I created a field with an autoincrement ID and not Null, primary key
> When I try to insert a record with no value in that specific field I
> get an error that the field must contain a value... this is strange,
> since I thought that the value would have been generated before
> insert (as how the trigger is made).
> When I give a value, and then look at the created record, I see that
> the field value is one of the generator, which is ok.
> Now, I made the field again and gave it a default value of 0. Now
> when I try to create a new record, I get again the same error...
> while now it should work, since I give a default value!
> What am I doing wrong??

It's depending on how you're inserting this record.
Some Tools check the NOT NULL constraint before you're allowed to post
the record.

create table test (
test_id integer not null,
test_val varchar(10),
constraint pk_test primary key (test_id)

create trigger bi_test for test before insert position 0
if ( new.test_id IS NULL ) then new.test_id = GEN_ID ( G_TEST, 1 );

if you try to insert a record (for example with the ib_console) you
can't post the record since the console knows about the NOT NULL
constraint for the field test_id, thus preventing you from posting the
record (since test_id is included in a select *).

But if you do: SELECT TEST_VAL rom TEST and enter a value for TEST_VAL
it should work as desired since in this case test_id is not part of the
select statement thus not part of the insert statement either.

You could also issue an:

INSERT INTO TEST ( test_val ) VALUES ( 'my test' )
SQL-command, which should work, too.