KN> The need to change a schema on the fly is usually caused by
KN> bad design. What is the probvlem you try to resolve?
the problem is i have a table that should contain only the last xxx
inserts.
to do so i created a trigger:
CREATE TRIGGER trg_TrimMessdataLast
AFTER INSERT ON MessdataAll
WHEN (
SELECT count(*) FROM MessdataLast
WHERE
MeasuringMode=NEW.MeasuringMode AND
Maschinennummer=NEW.Maschinennummer AND Merkmalnummer=NEW.Merkmalnummer)
>= 100000
BEGIN
DELETE FROM MessdataLast WHERE
MeasuringMode=NEW.MeasuringMode AND
Maschinennummer=NEW.Maschinennummer AND
MessId=(
SELECT MIN(MessId) FROM MessdataLast
WHERE Maschinennummer=NEW.Maschinennummer
AND MeasuringMode=NEW.MeasuringMode);
END
this one will delete 1st entries whenever there are more than 100000
measurements (not datasets. one measurement consits of more than one
dataset, column "messid" defines the measurement).
i wanted to make the 100000 changeable so i tried to change the
trigger from within a trigger that gets fired when some specific
field in another table (that contains the "100000" changes).
so now i have changed the trigger to
CREATE TRIGGER trg_TrimMessdataLast
AFTER INSERT ON MessdataAll
WHEN (
SELECT count(*) FROM MessdataLast
WHERE
MeasuringMode=NEW.MeasuringMode AND
Maschinennummer=NEW.Maschinennummer AND Merkmalnummer=NEW.Merkmalnummer)
>= (Select IntValue FROM SystemData WHERE ID=10)
BEGIN
DELETE FROM MessdataLast WHERE
MeasuringMode=NEW.MeasuringMode AND
Maschinennummer=NEW.Maschinennummer AND
MessId=(
SELECT MIN(MessId) FROM MessdataLast
WHERE Maschinennummer=NEW.Maschinennummer
AND MeasuringMode=NEW.MeasuringMode);
END
(changed the WHERE-part in the WHEN-clause)
wich works bus has to do a select on every trigger-call.
i wanted to have a fixed number there
greetings
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
