sqlsqlitetriggerssql-insertcalculated-columns

How do I create a SQLite trigger to automatically calculate columns after INSERT?

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?

Solution

  • 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