Re: [sqlite] Using variables within trigger definitions

[Dennis Cote]

Philipp Knüsel wrote:
Is there a possibility to define variables within trigger definitions?
I would like to to something like this (simplified example):
CREATE TRIGGER Side_Insert AFTER INSERT ON Side
BEGIN
    SET LOWDATE = SELECT MIN(Startdate) FROM Basis;
    ....
    INSERT INTO BASIS (Name,Startdate) VALUES ("Trigger", LOWDATE);
END;
I know, there is the possibility to do the same with SUBSELECTS,
but I'm looking for something easier/faster/more elegant than doing 
the same subselect several times.
(as my real example is more complicated)

Philipp,
You can't have variables in SQLite, but you can use another table to 
store the result of your complex query (which only executes once) and 
use simple subselects to retrieve that value. Something like this:

CREATE TABLE LowDate (date integer);
INSERT INTO LowDate VALUES(0);
CREATE TRIGGER Side_Insert AFTER INSERT ON Side
BEGIN
   UPDATE LowDate SET date = SELECT MIN(Startdate) FROM Basis;
   ....
   INSERT INTO BASIS (Name,Startdate)
       VALUES ("Trigger", (SELECT date FROM LowDate ));
END;
HTH
Dennis Cote