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:

Before Insert
Position 0
Active

I then used the following code:

BEGIN
IF ( ( NEW.MemberID IS NULL) OR NEW.MemberID <= 0) ) THEN
NEW.MemberID = GEN_ID(gen_MemberID,1);
END

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?

The problem with your trigger isn't obvious to me. It is almost identical to the trigger in this tutorial:

http://www.fingerbird.de/generatorg..._AutoInc_fields

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.

You're right! The missing "(" was a typo in the post.

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?

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:

BEGIN
IF ( ( NEW."MemberID" IS NULL) OR (NEW."MemberID" <= 0) ) THEN
NEW."MemberID" = GEN_ID(gen_MemberID,1);
END

everything would have worked correctly.

Thanks dcaillouet for your help!