small-j small-j - 7 months ago 19
SQL Question

Token unknown error in update Trigger

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

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;