[sqlite] Using variables within trigger definitions

[Philipp Knüsel]

Hello SQLite Users

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)

Even if there is no possibility right know, I would probably suggest
this as a feature for the future.

Thanks a lot!

Philipp

[Darren Duncan]

The SQL:2003 standard says you use syntax like this to do what you want:

CREATE TRIGGER Side_Insert AFTER INSERT ON Side
BEGIN
DECLARE LowDate DATE;
SELECT MIN(Startdate) INTO LowDate FROM Basis;
....
INSERT INTO BASIS (Name,Startdate) VALUES ("Trigger", LowDate);
END;

You use 'declare' to declare a variable. Also,
you only use 'set' when assigning the value of
another variable or expression. You do not use
'set' to retrive the value of a query, but 'into'
instead.

I don't know if SQLite supports this feature, though.

-- Darren Duncan

Post by Philipp Knüsel
Hello SQLite Users
Is there a possibility to define variables within trigger definitions?
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)
Even if there is no possibility right know, I
would probably suggest this as a feature for the
future.
Thanks a lot!
Philipp

[Philipp Knüsel]

Thank you Darren,

Unfortunately SQLite does not supports this, I just checked.

Kind regards

Philipp

Post by Philipp Knüsel
CREATE TRIGGER Side_Insert AFTER INSERT ON Side
BEGIN
DECLARE LowDate DATE;
SELECT MIN(Startdate) INTO LowDate FROM Basis;
....
INSERT INTO BASIS (Name,Startdate) VALUES ("Trigger", LowDate);
END;
You use 'declare' to declare a variable. Also, you only use 'set'
when assigning the value of another variable or expression. You do
not use 'set' to retrive the value of a query, but 'into' instead.
I don't know if SQLite supports this feature, though.
-- Darren Duncan

Post by Philipp Knüsel
Hello SQLite Users
Is there a possibility to define variables within trigger definitions?
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)
Even if there is no possibility right know, I would probably suggest
this as a feature for the future.
Thanks a lot!
Philipp

[Dennis Cote]

Post by Philipp Knüsel
Is there a possibility to define variables within trigger definitions?
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