1

This question follows on from my previous question here, which I have a working answer to:

but now I need to do the same in SQLite. I have read this question:

but it lacks sufficient detail for me to make use of it, hence this more detailed question.

Given this table:

CREATE TABLE myTable 
(
    datetime TEXT,
    gen_year INT GENERATED ALWAYS AS (SUBSTRING(datetime,7,4)) STORED,
    gen_mon INT GENERATED ALWAYS AS (SUBSTRING(datetime,4,2)) STORED,
    gen_date INT GENERATED ALWAYS AS (SUBSTRING(datetime,1,2)) STORED,
    gen_hr INT GENERATED ALWAYS AS (SUBSTRING(datetime,12,2)) STORED,
    gen_min INT GENERATED ALWAYS AS (SUBSTRING(datetime,15,2)) STORED,
    gen_sec INT GENERATED ALWAYS AS (SUBSTRING(datetime,18,2)) STORED,
    gen_ms INT GENERATED ALWAYS AS (SUBSTRING(datetime,21,3)) STORED,
    gen_isodate TEXT GENERATED ALWAYS AS (
        FORMAT("%04d",gen_year) || "-" ||
        FORMAT("%02d",gen_mon) || "-" ||
        FORMAT("%02d",gen_date) || " " ||
        FORMAT("%02d",gen_hr) || ":" ||
        FORMAT("%02d",gen_min) || ":" ||
        FORMAT("%02d",gen_sec) || "." ||
        FORMAT("%03d",gen_ms)
    ) STORED,
    sys_id INT,
    cputil REAL,
    memfree REAL,
    sessnum INT,
    util_lag REAL,   -- meant to be filled by TRIGGER upon/after INSERT
    mem_lag REAL,    -- meant to be filled by TRIGGER upon/after INSERT
    util_diff REAL,  -- meant to be filled by TRIGGER upon/after INSERT
    mem_diff REAL,   -- meant to be filled by TRIGGER upon/after INSERT
    util_change TEXT -- meant to be filled by TRIGGER upon/after INSERT
);

I am trying to use a trigger to calculate differences in the cputil and memfree columns between ROWs, and add a TEXT string to the util_change column, all upon INSERT for each new row.

I have the following trigger which is accepted (i.e. it throws no errors) - created with the help of ChatGPT:

-- chatGPT v1 with updates
CREATE TRIGGER tr_fill_calculated_columns
    AFTER INSERT ON myTable
BEGIN
    UPDATE myTable
    SET util_lag = (SELECT LAG(cputil) OVER (ORDER BY gen_isodate ASC) FROM myTable WHERE datetime = NEW.datetime),
        mem_lag = (SELECT LAG(memfree) OVER (ORDER BY gen_isodate ASC) FROM myTable WHERE datetime = NEW.datetime),
        util_diff = NEW.cputil - NEW.util_lag,
        mem_diff = NEW.freemem - NEW.mem_lag
    WHERE datetime = NEW.datetime;
END;

but the problem is it simply does not work - the trigger fails to calculate the columns I need it to. When I do an INSERT with the following data:

INSERT INTO myTable (datetime,sys_id,cputil,memfree,sessnum) 
VALUES
 ("06.03.2021 23:10:49.057",100,0.5,0.9,97)
,("24.03.2021 17:04:20.715",100,0.28,0.19,167)
,("09.06.2021 10:24:09.880",100,0.59,0.25,138)
,("30.06.2021 12:41:38.694",100,0.34,0.49,102)
,("28.07.2021 23:12:40.555",100,0.84,0.03,95)
,("22.10.2021 03:55:31.215",100,0.44,0.04,56)
,("25.08.2022 11:11:01.672",100,0.7,0.58,120)
,("25.08.2022 11:11:02.119",100,0.97,0.18,155)
,("25.08.2022 11:11:03.893",100,0.68,0.16,123)
,("25.08.2022 11:11:04.390",100,0.25,0.67,167)
,("25.08.2022 11:11:05.538",100,0.48,0.4,169)
,("25.08.2022 11:11:06.204",100,0.96,0.47,180)
,("25.08.2022 11:11:07.070",100,0.94,0.07,95)
,("25.08.2022 11:11:08.845",100,0.5,0.48,132)
,("25.08.2022 11:11:09.919",100,0.02,0.07,154)
,("25.08.2022 11:11:10.280",100,0.41,0.05,64)
,("25.08.2022 11:11:11.100",100,0.68,0.77,88)
,("25.08.2022 11:11:12.687",100,0.81,0.05,57)
,("25.08.2022 11:11:13.707",100,0.25,0.71,159)
,("25.08.2022 11:11:14.922",100,0.23,0.58,193)
,("25.08.2022 11:11:15.836",100,0.87,0.32,158)
,("25.08.2022 11:11:16.695",100,0.53,0.46,177)
,("25.08.2022 11:11:17.576",100,0.77,0.46,188)
,("25.08.2022 11:11:18.932",100,0.45,0.47,56)
,("25.08.2022 11:11:19.638",100,0.87,0.41,184)
,("25.08.2022 11:11:20.489",100,0.55,0.05,54)
,("25.08.2022 11:11:21.404",100,0.31,0.02,72)
,("25.08.2022 11:11:22.704",100,0.78,0.52,152)
,("25.08.2022 11:11:23.166",100,0.12,0.34,119)
,("25.08.2022 11:11:24.067",100,0.98,0.68,102)
,("25.08.2022 11:11:25.423",100,0.85,0.81,136)
,("25.08.2022 11:11:26.544",100,0.91,0,169)
,("25.08.2022 11:11:27.835",100,0.82,0.95,186)
,("25.08.2022 11:11:28.055",100,0.53,0.35,50)
,("25.08.2022 11:11:29.769",100,0.39,0.79,144)
,("25.08.2022 11:11:30.935",100,0.39,0.15,180)
,("25.08.2022 11:11:31.153",100,0.76,0.26,116)
,("25.08.2022 11:11:32.305",100,0.76,0.06,175)
,("25.08.2022 11:11:33.392",100,0.91,0.98,173)
,("25.08.2022 11:11:34.458",100,0.39,0.18,111)
,("25.08.2022 11:11:35.227",100,0.73,0.31,75)
,("25.08.2022 11:11:36.584",100,0.8,0.58,56)
,("25.08.2022 11:11:37.619",100,0.11,0.84,51)
,("25.08.2022 11:11:38.407",100,0.67,0.85,166)
,("25.08.2022 11:11:39.070",100,0.28,0.31,159)
,("25.08.2022 11:11:40.674",100,0.05,0.38,176)
,("25.08.2022 11:11:41.422",100,0.87,0.47,110)
,("25.08.2022 11:11:42.235",100,0.64,0.97,194)
,("25.08.2022 11:11:43.780",100,0.07,0.36,63)
,("25.08.2022 11:11:44.903",100,0.5,0.17,89)
RETURNING *;

SELECT * FROM myTable;

util_lag, mem_lag, util_diff and mem_diff are all [NULL] for all rows, not just the first (expected).

Example output:

SELECT results

I also tried to add the final four calculation lines to the trigger for the util_change column as follows:

DROP TRIGGER tr_fill_calculated_columns;

CREATE TRIGGER tr_fill_calculated_columns
    AFTER INSERT ON myTable
BEGIN
    UPDATE myTable
    SET util_lag = (SELECT LAG(cputil) OVER (ORDER BY gen_isodate ASC) FROM myTable WHERE datetime = NEW.datetime),
        mem_lag = (SELECT LAG(memfree) OVER (ORDER BY gen_isodate ASC) FROM myTable WHERE datetime = NEW.datetime),
        util_diff = NEW.cputil - NEW.util_lag,
        mem_diff = NEW.memfree - NEW.mem_lag
        util_change = (CASE
            WHEN NEW.util_diff > 0 THEN 'Up'
            WHEN NEW.util_diff < 0 THEN 'Down'
            ELSE '')
    WHERE datetime = NEW.datetime;
END;

but this failed with the error:

SQL Error [2]: [SQLITE_ERROR] SQL error or missing database (near "util_change": syntax error)

I tried getting ChatGPT to help me add the final calculated column util_change, and it produced the following:

-- Create a trigger that fires after inserting a new row into my_table
CREATE TRIGGER tr_fill_calculated_columns
AFTER INSERT ON myTable
BEGIN
    -- Declare variables to store the previous row's values
    DECLARE prev_cputil REAL;
    DECLARE prev_freemem REAL;
    DECLARE util_diff REAL;
    DECLARE mem_diff REAL;
    DECLARE util_change TEXT;

    -- Get the previous row's values for cputil and freemem
    SELECT cputil, freemem
    INTO prev_cputil, prev_freemem
    FROM myTable
    WHERE datetime = (SELECT MAX(datetime) FROM myTable);

    -- Calculate the differences
    SET util_diff = NEW.cputil - prev_cputil;
    SET mem_diff = NEW.freemem - prev_freemem;

    -- Determine the value for util_change
    IF util_diff > 0 THEN
        SET util_change = 'Up';
    ELSEIF util_diff < 0 THEN
        SET util_change = 'Down';
    ELSE
        SET util_change = '';
    END IF;

    -- Update the inserted row with calculated values
    UPDATE myTable
    SET util_lag = prev_cputil,
        mem_lag = prev_freemem,
        util_diff = util_diff,
        mem_diff = mem_diff,
        util_change = util_change
    WHERE datetime = NEW.datetime;
END;

but this also just fails with the syntax error:

SQL Error [2]: [SQLITE_ERROR] SQL error or missing database (near "DECLARE": syntax error)

Can someone please provide corrections to my SQLite trigger code where it is needed?

Improve this question
4
  • 1
    Why do you want triggers? Generated columns (which you are already using in dates) should do what you want natively, such as the mem* calculations. Commented Mar 16, 2024 at 8:39
  • In case it is not obvious from the field names util_lag, mem_lag, I need to get values from the previous row, which as far as I know cannot be done without a TRIGGER .I probably should have called them prev_util and prev_mem - apologies for that. Unless you know of a way to get a value from the previous row that others do not, I am told a TRIGGER is necessary. Commented Mar 16, 2024 at 8:51
  • 1
    Well, for the SQL Error [2]: [SQLITE_ERROR] SQL error or missing database (near "util_change": syntax error) I believe your CASE is missing the END. There is also a comma missing before util_change. As for the ChatGPT example, I would guess it showed you a procedure and you moved it to a trigger, right? AFAIK, Sqlite doesn't allow DECLARE except in procedures (but I admit some time passed since I touched Sqlite). Commented Mar 16, 2024 at 17:39
  • The second chatGPT example is verbatum. I don't have the skill to convert a PROCEDURE to a TRIGGER. The only changes I made to the chatGPT v1 example was adding the LAG() function as I thought it should be used. I couldn't see how the initial example was getting values from the previous row, which is what is need for a 'difference' calculation. Commented Mar 17, 2024 at 23:26

2 Answers 2

Reset to default
1

As an alternative to a trigger that modifies the insert on the fly in an instead of action, you can modify the row after the fact: demo.

CREATE INDEX dtidx ON myTable (datetime);

CREATE TRIGGER cust_addr_chng
AFTER INSERT ON myTable 
BEGIN
  UPDATE myTable
  SET util_lag    =previous.cputil,
      mem_lag     =previous.memfree,
      util_diff   =NEW.cputil-previous.cputil,
      mem_diff    =NEW.memfree-previous.memfree,
      util_change =CASE WHEN NEW.cputil-previous.cputil > 0
                        THEN 'Up'
                        WHEN NEW.cputil-previous.cputil < 0
                        THEN 'Down'
                        WHEN NEW.cputil-previous.cputil = 0
                        THEN ''
                   END
  FROM (SELECT * FROM myTable
        ORDER BY datetime DESC
        LIMIT 1, 1) AS previous
  WHERE myTable.datetime=NEW.datetime;
END;

In the after scenario, the new row is now the last one in the table so you need LIMIT 1, 1 to fetch second-but-last as its lag/previous.

datetime sys_id cputil memfree sessnum util_lag mem_lag util_diff mem_diff util_change
2019/05/03 08:06:14 100 0.57 0.51 47 null null null null null
2019/05/03 08:11:14 100 0.47 0.62 43 0.57 0.51 -0.09999999999999998 0.10999999999999999 Down
2019/05/03 08:16:14 100 0.56 0.57 62 0.47 0.62 0.09000000000000008 -0.050000000000000044 Up
2019/05/03 08:21:14 100 0.57 0.56 50 0.56 0.57 0.009999999999999898 -0.009999999999999898 Up
2019/05/03 08:26:14 100 0.35 0.46 43 0.57 0.56 -0.21999999999999997 -0.10000000000000003 Down
2019/05/03 08:31:14 100 0.41 0.58 48 0.35 0.46 0.06 0.11999999999999994 Up
Improve this answer
Sign up to request clarification or add additional context in comments.

17 Comments

It could seem like lag() simply means previous row, but note that it's a window function. It's the previous row in the window that narrows down a subset of what's selected. You probably could apply it, but it won't make much sense - it's useful when you have a set of rows and you want to somehow correlate each to their predecessor. Meanwhile, "SQLite supports only FOR EACH ROW triggers, not FOR EACH STATEMENT triggers" so you're only ever dealing with just one row and you only ever need to pinpoint the predecessor of that single row.
In the previous thread I mentioned lag() could probably do wonders in a statement-level trigger - for a multi-row insert you'd just get one "bootstrap row", the latest in the table that chronologically precedes the first one in the incoming batch, you'd give that for the first row to _diff off of, then use lag() for the rest of the batch.
As to the "anomaly", I'd have to see what you're doing exactly. Both of my answers link a demo, and those works fine regardless of whether you insert multiple at once or do it one by one. Here's one where I split the batch and insert it one by one, then inspect after each insert, and later I insert the rest all at once: demo. If you can reproduce the behaviour you experienced but do that on db<>fiddle, hit run and share a link to that, I'll take a look. Are you running the inserts from an app using a pool? From separate tabs/sessions in DBeaver?
I just noticed that some of your timestamps are in different format: that could be the culprit. SQLite only accepts ISO-8601 YYYY-MM-DD HH:MM:SS. If you use anything else, it will quietly assume it is just some text, so the entire logic dictating what is considered the last row, changes completely.
Here’s your fiddle, fixed: dbfiddle.uk/TGLeFxkD it did not like the double quotes in place of single quotes and it did not like multiple commands in one block.
|
1

You can redirect and modify inserts on a view from an instead of trigger: demo.

A bootstrap row will simplify the trigger:

insert into myTable(datetime)
select '-infinity';

Without it, the trigger would have to always consider two cases:

  • if there are no rows, insert incoming row without modifying it
  • if there are any rows, calculate the relative _lag and _diff values

By adding the initial row ahead of time you don't need to consider the first case.

CREATE INDEX dtidx ON myTable (datetime);

CREATE VIEW v_myTable AS SELECT * FROM myTable;

CREATE TRIGGER cust_addr_chng
INSTEAD OF INSERT ON v_myTable 
BEGIN
  INSERT INTO myTable
  SELECT NEW.datetime,
         NEW.sys_id,
         NEW.cputil,
         NEW.memfree,
         NEW.sessnum,
         previous.cputil,--util_lag
         previous.memfree,--mem_lag
         NEW.cputil-previous.cputil,--util_diff 
         NEW.memfree-previous.memfree,--mem_diff
         CASE WHEN NEW.cputil-previous.cputil > 0
              THEN 'Up'
              WHEN NEW.cputil-previous.cputil < 0
              THEN 'Down'
              WHEN NEW.cputil-previous.cputil = 0
              THEN ''
         END --util_change
  FROM myTable AS previous
  ORDER BY datetime DESC
  LIMIT 1;
END;
datetime sys_id cputil memfree sessnum util_lag mem_lag util_diff mem_diff util_change
-infinity null null null null null null null null null
2019/05/03 08:06:14 100 0.57 0.51 47 null null null null null
2019/05/03 08:11:14 100 0.47 0.62 43 0.57 0.51 -0.09999999999999998 0.10999999999999999 Down
2019/05/03 08:16:14 100 0.56 0.57 62 0.47 0.62 0.09000000000000008 -0.050000000000000044 Up
2019/05/03 08:21:14 100 0.57 0.56 50 0.56 0.57 0.009999999999999898 -0.009999999999999898 Up
2019/05/03 08:26:14 100 0.35 0.46 43 0.57 0.56 -0.21999999999999997 -0.10000000000000003 Down
2019/05/03 08:31:14 100 0.41 0.58 48 0.35 0.46 0.06 0.11999999999999994 Up
Improve this answer

9 Comments

I have never hear the term "bootstrap row" before. I think I understand it, but not sure. In any case, I am hoping to avoid creating a view because unless materialized my understanding of a view is that it is only temporary, i.e. in memory. If/when the machine gets rebooted, I do not want all those calculated column values to be lost, they meant to be permanent. Again, we are talking hundreds of millions of rows (initial calculation will be slow, but maintenance calculations as new rows are added should be very fast).
I just noticed you LIMIT the view to the last row. Correct me if I am wrong: does it create a view as a placeholder only for the row being inserted, calculate the values for the view row, then write the view row to the real table?
I wouldn't worry about it living in the memory or being lost on reboot. From the doc: "CREATE VIEW command assigns a name to a pre-packaged SELECT statement" - most RDBMS do it like that. It takes zero space, and selects from the view are pretty much translated to selects from a subquery containing its definition. The trigger on the view just re-routes insert statements to save the incoming rows to the underlying table - they are persisted in it the same way they would be if you inserted them directly.
The view definition doesn't really matter: the view is just used here as a point where you hijack and modify the insert on the fly, which isn't possible for direct inserts into the underlying table. As to the null results for calculated columns, my guess would be that you inserted rows directly into the table, which won't activate the trigger on the view, or you inspected rows inserted into the view via returning clause - in which case you'd see their state prior to the modification by the trigger: demo.
It was also in my opening statement all along :) It is SQLite’s implementation detail that you can’t otherwise hijack the operation - the trigger acts like a filter, but the view is just a way to get that instead of trigger. Only views can have those
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.