Token unknown error in update Trigger

Question

Here's the update trigger I'm trying to create for a view. It's pretty clear what I'm trying to achieve:

SET TERM !! ;
CREATE TRIGGER SST_Insert FOR SpaceSpaceTypes
BEFORE INSERT AS
DECLARE VARIABLE NEW_TYPE_ID;
BEGIN
NEW_TYPE_ID=(SELECT MAX(type_id) FROM SpaceTypes WHERE type_name=NEW.type_name);
INSERT INTO Space values (NEW.address,NEW.company_id,NEW.area,NEW_TYPE_ID);
END!!
SET TERM ; !!

I've tried different combos of !!'s and ;'s. I tried dropping a variable. It didn't help. The error for the query above is: Statement failed, SQLSTATE = 42000

Dynamic SQL Error -SQL error code = -104 -Token unknown - line 3, column 29 -;

Answer 1

The problem is with this line:

DECLARE VARIABLE NEW_TYPE_ID;

You haven't specified the type of NEW_TYPE_ID, so the statement is incomplete and the semi-colon is unexpected at that position, which causes a token unknown error.

The syntax of DECLARE VARIABLE is:

DECLARE [VARIABLE] <varname>
  {<datatype> | <domain> | TYPE OF {<domain> | COLUMN <rel.col>}
    [NOT NULL]  [CHARACTER SET <charset>] [COLLATE <collation>]
    [{DEFAULT | = } <initvalue>];

So at minimum you need to use:

DECLARE VARIABLE NEW_TYPE_ID INTEGER;

Or more advanced:

DECLARE VARIABLE NEW_TYPE_ID TYPE OF COLUMN SpaceTypes.type_id;

Answer 2

The best option was to leave it as

   SET TERM !! ;
    CREATE TRIGGER SST_Insert FOR SpaceSpaceTypes
    BEFORE INSERT AS
    BEGIN
    INSERT INTO Space values (NEW.address,NEW.company_id,NEW.area,(SELECT        MAX(type_id) FROM SpaceTypes WHERE type_name=NEW.type_name));
END!!
SET TERM ; !!