On Fri, 2 Oct 2009 12:18:11 +0200, Michael Werber
<[email protected]> wrote:
>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
Not a bad design after all.
I wouldn't worry about the
(Select IntValue FROM SystemData WHERE ID=10)
For this kind of use, the SystemData table will occupy just
one page, and a page for the primary key index.
Not much of a burden for the page cache (default 2000
database pages).
--
( Kees Nuyt
)
c[_]
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
