I'm not sure that CTE's are allowed inside triggers, but even so, it will need
to be appended to each update. I think a real table (albeit a temporary one)
will do much better - It should simply be a placeholder for variables, but has
the benefit of being able to be joined to other queries, etc.
This SQL code should work, though I can't test it since I don't have the DB,
you might need to check it for syntax, but the premise should definitely work
well. The left-joins will leave NULLs where items do not match and so the
updates will ignore them (or the COALESCE will ignore in case some states exist
in both countries).
DROP TRIGGER IF EXISTS _160m_Increment;
CREATE TRIGGER _160m_Increment
AFTER UPDATE ON qso WHEN
NEW.freq LIKE '1.%'
BEGIN
CREATE TEMPORARY TABLE IF NOT EXISTS t160 (c TEXT, s TEXT, i_usa INT, i_mex
INT, i_can INT, i_wld INT);
DELETE FROM t160;
INSERT INTO t160 (c, s, i_usa, i_mex, i_can, i_wld)
SELECT qth.country, qth.state, u._160m+1, m._160m+1, c._160m+1, w._160m+1
FROM qth WHERE rowid = NEW.qth_other_id
LEFT JOIN usa_state AS u ON u.state = qth.state
LEFT JOIN mx_state AS m ON m.state = qth.state
LEFT JOIN ca_prov AS c ON c.prov = qth.state
LEFT JOIN world AS w ON w.country = qth.country
WHERE qth.rowid = NEW.qth_other_id LIMIT 1;
UPDATE usa_state SET _160m = COALESCE((SELECT i_usa FROM t_160), _160m)
WHERE state = (SELECT s FROM t160);
UPDATE mx_state SET _160m = COALESCE((SELECT i_mex FROM t_160), _160m)
WHERE state = (SELECT s FROM t160);
UPDATE ca_prov SET _160m = COALESCE((SELECT i_can FROM t_160), _160m)
WHERE prov = (SELECT s FROM t160);
UPDATE world SET _160m = COALESCE((SELECT i_wld FROM t_160), _160m)
WHERE country = (SELECT c FROM t160);
END;
We never drop that temp table, since it is faster to just re-use it, and it
will get dropped automatically when you close the connection - but that means
we need to truncate it on use, hence the DELETE FROM.
Good luck!
Ryan
On 2017/01/07 5:34 PM, Kees Nuyt wrote:
On Sat, 7 Jan 2017 09:27:57 -0500, Gan Uesli Starling
<g...@starling.us> wrote:
So I'm trying to accumulate data for state/prov inside of USA, Canada
and Mexico, and country for the rest of the world.
Since country and state from the same update are factors in incrementing
each of four tables, I was hoping to simplify it with a single select
with 'AS' and then re-use the AS-value in every case. This as opposed to
the redundancy of selecting the same thing anew every time. See as
below. It passes syntax but fails on exectute, saying there is no column
's'. Is there some way to do this? To hold the 'state' and 'country' and
reuse them like a variable from one query to the next for sequential
comparisons?
SQLite doesn't support variables in the way you try to use here.
You can try one of the following:
1- Replace every c and s in the UPDATE statements
by (SELECT ...)
2- prefix every UPDATE with a common tabel expression
that delivers the reuired values, and use them with
a (SELECT ...) as above
3- perhaps do something smart with an updateble view using an
INSTEAD OF trigger, and supply the values once to the
UPDATE myview statement.
-- Trigger for incrementing country & region counts for 160m band,
QSO-by-QSO.
DROP TRIGGER IF EXISTS _160m_Increment;
CREATE TRIGGER _160m_Increment
AFTER UPDATE ON qso WHEN
NEW.freq LIKE '1.%'
BEGIN
SELECT country AS c FROM qth WHERE rowid = NEW.qth_other_id;
SELECT state AS s FROM qth WHERE rowid = NEW.qth_other_id;
UPDATE usa_state SET _160m = 1 + (SELECT _160m FROM usa_state WHERE
state = s) WHERE state = s AND c = 'USA';
UPDATE mx_state SET _160m = 1 + (SELECT _160m FROM mx_state WHERE
state = s) WHERE state = s AND c = 'Mexico';
UPDATE ca_prov SET _160m = 1 + (SELECT _160m FROM ca_prov WHERE
prov = s) WHERE prov = s AND c = 'Canada';
UPDATE world SET _160m = 1 + (SELECT _160m FROM world WHERE
country = c) WHERE country = c;
END;
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
