Hi,

I have just installed firebird 2.1 and tried to create a simple table
with a sequence and trigger to insert a unique primary key. I think I
just copied the example from the manual, but I cannot make it run. I
always get a syntax error (invalid token). What's wrong?


My creation script:
create table genres (
genreId int not null primary key,
genre varchar(255),
cnt int,
constraint genreIndex unique ( genre )
);
create sequence seq_genres;
create trigger trg_genres for genres
active before insert position 0
as
BEGIN
if ((new.genreId is null) or (new.genreId = 0)) then
begin
new.genreId = next value for seq_genres;
end
end
;


The result:
Starting transaction...
Preparing query: create table genres (
genreId int not null primary key,
genre varchar(255),
cnt int,
constraint genreIndex unique ( genre )
)
Prepare time: 00:00:00.
Plan not available.


Executing...
Done.
378 fetches, 71 marks, 0 reads, 0 writes.
20 inserts, 2 updates, 0 deletes, 31 index, 0 seq.
Delta memory: 9168 bytes.
RDB$FIELDS: 3 inserts.
RDB$INDEX_SEGMENTS: 2 inserts.
RDB$INDICES: 2 inserts.
RDB$RELATION_FIELDS: 3 inserts.
RDB$RELATIONS: 1 inserts. 2 updates.
RDB$USER_PRIVILEGES: 5 inserts.
RDB$RELATION_CONSTRAINTS: 3 inserts.
RDB$CHECK_CONSTRAINTS: 1 inserts.
Execute time: 00:00:00.
Preparing query:
create sequence seq_genres
Prepare time: 00:00:00.
Plan not available.


Executing...
Done.
7 fetches, 3 marks, 0 reads, 0 writes.
1 inserts, 0 updates, 0 deletes, 0 index, 0 seq.
Delta memory: 192 bytes.
RDB$GENERATORS: 1 inserts.
Execute time: 00:00:00.
Preparing query:
create trigger trg_genres for genres
active before insert position 0
as
BEGIN
if ((new.genreId is null) or (new.genreId = 0)) then
begin
new.genreId = next value for seq_genres
*** IBPP::SQLException ***
Context: Statement::Prepare(
create trigger trg_genres for genres
active before insert position 0
as
BEGIN
if ((new.genreId is null) or (new.genreId = 0)) then
begin
new.genreId = next value for seq_genres )
Message: isc_dsql_prepare failed

SQL Message : -104
Invalid token

Engine Code : 335544569
Engine Message :
Dynamic SQL Error
SQL error code = -104
Unexpected end of command - line 8, column 38


Execute time: 00:00:00.


Any help is much appreciated.

Regards
Helmut