#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Location
    NC
    Posts
    4
    Rep Power
    0

    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?
  2. #2
  3. Big Endian
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    May 2001
    Location
    Fly-over country
    Posts
    1,172
    Rep Power
    29
    The problem with your trigger isn't obvious to me. It is almost identical to the trigger in this tutorial:

    http://www.fingerbird.de/generatorgu...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.
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Location
    NC
    Posts
    4
    Rep Power
    0
    You're right! The missing "(" was a typo in the post.
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Location
    NC
    Posts
    4
    Rep Power
    0

    Question


    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?
  8. #5
  9. Big Endian
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    May 2001
    Location
    Fly-over country
    Posts
    1,172
    Rep Power
    29
  10. #6
  11. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Location
    NC
    Posts
    4
    Rep Power
    0

    Thumbs up


    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!

IMN logo majestic logo threadwatch logo seochat tools logo