I have a table that I tried to modify through DB Workbench. I wanted to create indexes for some fields, and add a new column that I intended to use as a primary key populated through a generator/trigger (though in several steps). Doing this, I observed that values changed in other fields than I specified.

Trying to make a reproducible test case, I came up with the following:

create table Test(Field1 Integer);
commit;

insert into Test values(20);
insert into test values(18);
insert into test values(16);
commit;

alter table test
add id integer;

create index test_idx on test(Field1);

/*a commit here prevents the error from happening*/

create generator test_gen;

SET TERM ^^ ;
CREATE TRIGGER TEST_ID FOR TEST ACTIVE BEFORE INSERT POSITION 0 AS
begin
if ( (new.ID is null) or (new.ID = 0) )
then new.ID = gen_id(test_GEN, 1);
end
^^
SET TERM ; ^^

commit;

Now, I would expect FIELD1 to contain the values I inserted, and ID to be NULL. To my surprise, ID contains the same values as FIELD1.

Then, doing

UPDATE TEST SET ID = gen_id(test_GEN, 1)

updates both FIELD1 and ID!

In my case, it was simple to recreate the table and do my DDL in separate transactions, but I was certainly surprised that it was this easy (particulary with DBWs option for CREATE TRIGGER AND GENERATOR) to make Firebird create garbage in the table.

Is this a known issue/user error and/or is it reproducible in other versions of Firebird?

Set


[Non-text portions of this message have been removed]