October 8th, 2003, 11:01 AM
Creating autoinc trigger in Firebird
I am a newcomer to Firebird and need a little help. I have been using IBOConsole with Firebird 1.5 and have successfully created a databse and tables. However, in one of the tables I wanted to set an autoincrement id field. Using IBOConsole, I set the following parameters in the New Trigger wizard:
I then used the following code:
IF ( ( NEW.MemberID IS NULL) OR NEW.MemberID <= 0) ) THEN
NEW.MemberID = GEN_ID(gen_MemberID,1);
MemberID is an integer column in my table and I had previously created the generator gen_MemberID.
When I click on the compile button, I get a message 'UNKNOWN COLUMN: NEW.MEMBERID'.
Do any of you more experienced Firebird users have any idea why?
October 8th, 2003, 11:30 AM
The problem with your trigger isn't obvious to me. It is almost identical to the trigger in this tutorial:
I'm assuming that your missing "(" after the "OR" is a typo. Otherwise if MemberID is a column in the table the trigger is being attached to, then I don't understand why you're getting that particular error message.
October 8th, 2003, 04:10 PM
You're right! The missing "(" was a typo in the post.
October 13th, 2003, 07:10 PM
I discovered that if I recreated the database specifying SQL dialect 1, the problem doesn't exist (the original db was dialect 3). Can anyone explain this or point me to a complete explanation of the differences between the two dialects?
October 14th, 2003, 07:52 AM
October 16th, 2003, 11:58 AM
For all of you that may encounter a similar problem, it appears to be related to my use of IBOConsole to create my tables. Apparently, IBOConsole wraps the field names in double quotes by default if using SQL Dialect 3. To refer to the fields later, they must again be wrapped in double quotes. If my code had been:
IF ( ( NEW."MemberID" IS NULL) OR (NEW."MemberID" <= 0) ) THEN
NEW."MemberID" = GEN_ID(gen_MemberID,1);
everything would have worked correctly.
Thanks dcaillouet for your help!