| Subject | Re: [firebird-support] Can't create Generator |
|---|---|
| Author | Helen Borrie |
| Post date | 2011-03-08T21:43:20Z |
At 08:47 AM 9/03/2011, you wrote:
All that is happening here is that you are trying to create a generator using a name that already exists. Use another name and all will be well.
It might be a gotcha with the IBExpert tool, or the way you are using it.
Check the text of your Before Insert trigger: your difficulties might well be coming from a typo in that.
Suppose you have a generator called MY_GEN that you want to use for the primary key index values (or some other unique column) in table MY_TABLE, where the PK column is called A_PK.
First verify that MY_GEN exists (select * from RDB$GENERATORS WHERE RDB$GENERATOR_NAME = 'MYGEN')
(If you can't create it then it *must* exist....)
then
CREATE TRIGGER AUTO_INC_MY_TABLE_PK) FOR MY_TABLE
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.A_PK IS NULL) THEN
NEW.A_PK = GEN_ID (MY_GEN, 1);
END
Don't forget to commit it!
Tip: I don't know anything about recent versions of IBExpert...but if you want a free toolset that can show you the system tables in a compact and comprehensible way, go to www.ibobjects.com and download IB_SQL. On the Browse tool, you can check a box (labelled 'Sys. Inf.' to show all the information about the sys tables in grids.
./heLen
>I'm using IBExpert, but I think this is a Firebird issue. If not, just let me know and I'll check with HK.RDB$INDEX_11 is the unique index on the system table RDB$GENERATORS. If you drop that, you will corrupt your database.
>
>I foolishly forgot to create and AutoInc generator when I defined a new table. Then I added a record, and deleted it. Now when I try to add the generator I get:
>DEFINE GENERATOR failed.
>attempt to store duplicate value (visible to active transactions) in unique index "RDB$INDEX_11".
All that is happening here is that you are trying to create a generator using a name that already exists. Use another name and all will be well.
>Deleting and recreating the PK field didn't change the message, nor did dropping the table and recreating it. I'm using the same table and field names each time because they adhere to the standards I've used throughout the project. I don't know what else to do other than drop RDB$INDEX_11, but I don't know what else that might do.Generators and the tables you are feeding the values into are not related, so dropping the primary index or the table or anything else like that will have no effect on RDB$GENERATORS.
It might be a gotcha with the IBExpert tool, or the way you are using it.
Check the text of your Before Insert trigger: your difficulties might well be coming from a typo in that.
Suppose you have a generator called MY_GEN that you want to use for the primary key index values (or some other unique column) in table MY_TABLE, where the PK column is called A_PK.
First verify that MY_GEN exists (select * from RDB$GENERATORS WHERE RDB$GENERATOR_NAME = 'MYGEN')
(If you can't create it then it *must* exist....)
then
CREATE TRIGGER AUTO_INC_MY_TABLE_PK) FOR MY_TABLE
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.A_PK IS NULL) THEN
NEW.A_PK = GEN_ID (MY_GEN, 1);
END
Don't forget to commit it!
Tip: I don't know anything about recent versions of IBExpert...but if you want a free toolset that can show you the system tables in a compact and comprehensible way, go to www.ibobjects.com and download IB_SQL. On the Browse tool, you can check a box (labelled 'Sys. Inf.' to show all the information about the sys tables in grids.
./heLen