Open Office Base: How to Create a INSERT Trigger

Question

I am creating a database using OpenOffice Base and some homebrewed sql. Problems come in when trying to reference a foreign key that is numbers and possibly letters. I would like to force the table I am checking the key on to force the inserts to uppercase (or lower, I am not picky) and do the same to the table that is doing the checking. The tables:

DROP TABLE IF EXISTS Gun;
DROP TABLE IF EXISTS Model;  
DROP TABLE IF EXISTS Value;

CREATE TABLE Model (
    "Model Name" VARCHAR (25) NOT NULL PRIMARY KEY,
    "Manufacuture" VARCHAR (15) NOT NULL,
    "Manufactured Start Year" INTEGER NOT NULL,
    "Manufactured End Year" INTEGER,
    "Gun Type" VARCHAR (10),
    "Value Mininum" DECIMAL (10, 2),
    "Value Maximum" DECIMAL (10, 2) NOT NULL,
    "Description" VARCHAR (500)
);

CREATE TABLE Gun (
    "Serial Number" VARCHAR (25) NOT NULL,
    "Model Name" VARCHAR (25),
    "Caliber" VARCHAR (10),
    "Unique Features" VARCHAR (200),
    "Manufactured Date" INTEGER,
    "Condition" INTEGER,
    CONSTRAINT FK_MODEL FOREIGN KEY ("Model Name") REFERENCES Model ("Model Name"),
    CONSTRAINT PK_GUN_KEY PRIMARY KEY ("Serial Number", "Model Name"),
    CONSTRAINT CK_CONDITION CHECK( "Condition" <= 100 )    
);

The Trigger (that doesn't work and is one of many I have tried):

CREATE TRIGGER ForceUpper BEFORE INSERT ON Model
SET Model."Model Name" = UPPER(Model."Model Name");

Which gives:

Unexpected end of command: SET in statement

Any tips on what I am doing wrong would be nice. The HyperSQL docs I looked at were of little help besides adding BEGIN ATOMIC which also had unexpected end of command (even with END). Thanks

Answer 1

Checking HSQLDB documentation, I think your syntax should be:

create trigger ForceUpper before insert on Model
    for each row set new."Model Name" = UPPER(new."Model Name")

That new keyword refers to the new row that is being inserted

Note: I suggest you avoid using spaces in table and/or column names. If you need to separate words, either use _ instead of spaces or use CamelCase.