non openSUSE specific. SQLite trigger as FK replacement

English Programming/Scripting
#1

Thought I’d might as well give it a go here, the idea is to delete anything related to ‘stationsnummer’, like how in MYSQL you can set foreign keys to cascade.

CREATE TRIGGER weerstation_del_fk
BEFORE DELETE ON weerstations
FOR EACH ROW BEGIN 
    DELETE from waarnemingen WHERE waarnemingen.stationsnummer = OLD.stationsnummer;
END;

It’s not giving any information other then
near “stationsnummer”: syntax error

Code for the 2 tables in question if you feel like recreating the situation:

CREATE TABLE [waarnemingen] ( [stationsnummer] VARCHAR NOT NULL,  [datum] Date NOT NULL,  [windrichting] int,  [etmaalwind] int,  [uurwind] int,  [maxwind] int,  [etmaaltemp] int,  [mintemp] int,  [maxtemp] int,  [zonneschijn] int,  [langstezon] int,  [duurneerslag] int,  [etmaalneerslag] int,  [etmaaldruk] int(5),  [zicht] int,  [bovenlucht] int,  [vochtigheid] int,  CONSTRAINT [sqlite_autoindex_waarnemingen_1] PRIMARY KEY ([stationsnummer], [datum]));

CREATE TABLE [weerstations] ( [stationsnummer] VARCHAR(5) NOT NULL,  [naam] String NOT NULL,  [positie] string NOT NULL,  [terreinhoogte] DOUBLE(2, 1) NOT NULL,  [karakteristiek] string NOT NULL,  [grondsoort] string NOT NULL,  [barometer] DOUBLE(3, 1) NOT NULL,  [windmeetmast] INT(2) NOT NULL,  [voortzetting] string,  [positie2] string,  [terreinhoogte2] string,  [metingen2] string,  CONSTRAINT [sqlite_autoindex_weerstations_1] PRIMARY KEY ([stationsnummer]));

Based it off this site
Many thanks if you know the answer :wink:

#2

Well figured out my problem…

The tool I was using to test it in doesn’t work properly… executed pretty much the same stuff in java and it’s working.

So if anyone attempts to do this, don’t rely on sqlite expert (be it under wine or under windows).

PS:
Looking for a GOOD sqlite utility.

#3

You could try:

GPL:

Sqliteman - Sqlite Databases Made Easy
SQLiteStudio

Commercial:

SQLite Admin Tool - SQLite Database Browser by SQL Maestro Group

Others:

SQLite CVSTrac