"ambiguous column name" error from AFTER INSERT trigger

Question

In DBeaver version 23.3.4.202402060628 I have the following SQLite3 code (for a high speed injection moulded part and package tracking machine in a factory):

--------------------------------------------------------------------------------
-- SQLite TABLE
DROP TABLE IF EXISTS pkgdata;

CREATE TABLE pkgdata (
    DateTime TEXT, -- source format = "DD.MM.YYYY HH:mm:ss.sss"
    Weight REAL,
    Height REAL,
    Boxes INT,
    Parts INT,
    Spread REAL GENERATED ALWAYS AS (Weight - Height) STORED,
    gen_pkg_year  INT GENERATED ALWAYS AS (SUBSTRING(DateTime, 7, 4)) STORED,
    gen_pkg_month INT GENERATED ALWAYS AS (SUBSTRING(DateTime, 4, 2)) STORED,
    gen_pkg_day   INT GENERATED ALWAYS AS (SUBSTRING(DateTime, 1, 2)) STORED,
    gen_pkg_wkday INT GENERATED ALWAYS AS (strftime('%w',DateTime)) STORED,
    gen_pkg_per   TEXT GENERATED ALWAYS AS (strftime('%p',DateTime)) STORED,
    gen_pkg_hr    INT GENERATED ALWAYS AS (SUBSTRING(DateTime, 12, 2)) STORED,
    gen_pkg_min   INT GENERATED ALWAYS AS (SUBSTRING(DateTime, 15, 2)) STORED,
    gen_pkg_sec   INT GENERATED ALWAYS AS (SUBSTRING(DateTime, 18, 2)) STORED,
    gen_pkg_ms    INT GENERATED ALWAYS AS (SUBSTRING(DateTime, 21, 3)) STORED,
    gen_ISODate TEXT GENERATED ALWAYS AS (
        printf('%04d-%02d-%02d %02d:%02d:%02d.%03d',
        gen_pkg_year, gen_pkg_month, gen_pkg_day,
        gen_pkg_hr, gen_pkg_min, gen_pkg_sec, gen_pkg_ms)
    ) STORED,
    prev_Weight REAL,
    prev_Height REAL,
    Weight_diff REAL,
    Height_diff REAL,
    Weight_move TEXT,
    Height_move TEXT,
    gap      TEXT
);

--------------------------------------------------------------------------------
-- SQLite INDICES - created one line at a time
CREATE INDEX pkg_idx_ymd ON pkgdata (gen_pkg_year, gen_pkg_month, gen_pkg_day);
CREATE INDEX pkg_idx_mon ON pkgdata (gen_pkg_month);
CREATE INDEX pkg_idx_dom ON pkgdata (gen_pkg_day);
CREATE INDEX pkg_idx_dow ON pkgdata (gen_pkg_wkday);
CREATE INDEX pkg_idx_per ON pkgdata (gen_pkg_per);
CREATE INDEX pkg_idx_hr  ON pkgdata (gen_pkg_hr);
CREATE INDEX pkg_idx_min ON pkgdata (gen_pkg_min);

--------------------------------------------------------------------------------
-- SQLite TRIGGER
DROP TRIGGER IF EXISTS pkg_tr_fill_calculated_columns;

CREATE TRIGGER pkg_tr_fill_calculated_columns
    AFTER INSERT ON pkgdata
BEGIN
    UPDATE pkgdata
        SET prev_Weight = previous.Weight,
            prev_Height = previous.Height,
            Weight_diff = NEW.Weight - previous.Weight,
            Height_diff = NEW.Height - previous.Height,
            Weight_move = CASE
                WHEN Weight > prev_Weight THEN 'H'
                WHEN Weight = prev_Weight THEN 'S'
                WHEN Weight < prev_Weight THEN 'L'
            END,
            Height_move = CASE
                WHEN Height > prev_Height THEN 'H'
                WHEN Height = prev_Height THEN 'S'
                WHEN Height < prev_Height THEN 'L'
            END,
            gap = CASE
                WHEN Height > prev_Weight THEN 'U'
                WHEN Weight < prev_Height THEN 'D'
                ELSE ''
            END
    FROM (SELECT * FROM pkgdata
        ORDER BY gen_ISODate DESC
        LIMIT 1, 1) AS previous -- get the previous row
    WHERE pkgdata.DateTime = NEW.DateTime;
END;

--------------------------------------------------------------------------------
-- SQLite INSERT DATA
INSERT INTO pkgdata (
    DateTime, -- TEXT
    Weight,   -- REAL
    Height,   -- REAL
    Boxes,    -- INT
    Parts)    -- INT
VALUES
    ('01.01.2010 00:00:00.817',0.89755,0.89725,0,0),
    ('01.01.2010 00:00:01.157',0.89753,0.89728,0,0),
    ('01.01.2010 00:00:01.457',0.89755,0.89725,0,0),
    ('01.01.2010 00:00:01.737',0.89755,0.89725,0,0),
    ('01.01.2010 00:00:02.417',0.89755,0.89725,0,0),
    ('01.01.2010 00:00:02.747',0.8975,0.8973,0,0),
    ('01.01.2010 00:00:06.295',0.89755,0.89725,0,0),
    ('01.01.2010 00:00:06.566',0.89758,0.89723,0,0),
    ('01.01.2010 00:00:06.866',0.89755,0.89725,0,0),
    ('01.01.2010 00:00:09.985',0.89755,0.89725,0,0),
    ('01.01.2010 00:00:10.325',0.89755,0.89725,0,0),
    ('01.01.2010 00:00:11.055',0.89755,0.89725,0,0),
    ('01.01.2010 00:00:12.385',0.89755,0.89725,0,0),
    ('01.01.2010 00:00:12.604',0.8975,0.8973,0,0),
    ('01.01.2010 00:00:12.884',0.89755,0.89725,0,0),
    ('01.01.2010 00:00:13.114',0.89758,0.89723,0,0),
    ('01.01.2010 00:00:15.283',0.89758,0.89723,0,0),
    ('01.01.2010 00:00:15.514',0.8975,0.8973,0,0),
    ('01.01.2010 00:00:15.684',0.8975,0.8973,0,0),
    ('01.01.2010 00:00:17.083',0.8975,0.8973,0,0),
    ('01.01.2010 00:00:17.693',0.8975,0.8973,0,0)
    returning *;

I had working code but now I am getting an error when I get to the INSERT statement (DBeaver accepts each block):

SQL Error 1: [SQLITE_ERROR] SQL error or missing database (ambiguous column name: Weight)

There is only one table with one column named Weight. From this post I thought there is something still in memory, so I disconnected all other databases and deleted all tables from this one and started from scratch. I tried using fully qualified names by prepending table name:

INSERT INTO pkgdata (
    pkgdata.DateTime, -- TEXT
    pkgdata.Weight,   -- REAL
    pkgdata.Height,   -- REAL
    pkgdata.Boxes,    -- INT
    pkgdata.Parts)    -- INT
VALUES
...

But then it throws a syntax error on the period.

Ok, so I upgraded, and I created the table and indices but not the trigger, and INSERT worked. But I do need the trigger for the calculated columns - I need calculated columns to be calculated upon insertion of each row. This is time series data and this is critical — skeetastax
– skeetastax, Commented Jul 12, 2024 at 10:27
So now you know the problem is in the trigger. Next start debugging the update in it as a standalone query (using dummy values for the old row's fields) — Shawn
– Shawn, Commented Jul 12, 2024 at 18:19
Here it is in a DB fiddle: dbfiddle.uk/SSk1oWVf. It accepts the trigger syntax. I'm not entirely sure how to debug this. I had help constructing it (from my other post). Can you please explain what you mean by "debugging the update as a standalone query"? — skeetastax
– skeetastax, Commented Jul 12, 2024 at 21:44

skeetastax · Accepted Answer · 2024-07-16 22:42:14Z

"… but now I am getting an error when I get to the INSERT statement …"

AFTER INSERT trigger in its UPDATE event fails to distinguish between inserted (before trigger event) and updated (after trigger event) associated row on all column names of every WHEN clause in each of three CASE expressions, hence the "ambiguous column name" error.

"DBeaver accepts each block"

It doesn't result in syntax error when issuing CREATE TRIGGER because the CASE expression itself is valid (only in the context of a trigger event it becomes ambiguous).

"I tried using fully qualified names by prepending table name: … But then it throws a syntax error on the period."

That's expected.

Prepend column names with intended before- or after trigger event reference (NEW.column-name or OLD.column-name) on every WHEN clause in each of the trigger UPDATE event's CASE expressions. Also, consider using virtual columns (or the calculations that would generate them) in the CASE expressions' WHEN clauses rather than separately SET that data in the same trigger UPDATE event which calculations depend on it (redundantly duplicating in the process).

Corrected TRIGGER statement:

CREATE TRIGGER pkg_tr_fill_calculated_columns
    AFTER INSERT ON pkgdata
BEGIN
    UPDATE pkgdata
        SET prev_Weight = previous.Weight,
            prev_Height = previous.Height,
            Weight_diff = NEW.Weight - previous.Weight,
            Height_diff = NEW.Height - previous.Height,
            Weight_move = CASE
                WHEN NEW.Weight > previous.Weight THEN 'H'
                WHEN NEW.Weight = previous.Weight THEN 'S'
                WHEN NEW.Weight < previous.Weight THEN 'L'
            END,
            Height_move = CASE
                WHEN NEW.Height > previous.Height THEN 'H'
                WHEN NEW.Height = previous.Height THEN 'S'
                WHEN NEW.Height < previous.Height THEN 'L'
            END,
            gap = CASE
                WHEN NEW.Height > previous.Weight THEN 'U'
                WHEN NEW.Weight < previous.Height THEN 'D'
                ELSE ''
            END
    FROM (SELECT * FROM pkgdata
        ORDER BY gen_ISODate DESC
        LIMIT 1, 1) AS previous -- get the previous row
    WHERE pkgdata.DateTime = NEW.DateTime;
END;

Corrected dbfiddle.

Based on stuff I've read, virtual columns are only generated in memory and returned as results to a query - I want them permanently stored in the table as they will never change. I know this means more disk space to store the DB. I am planning on doing real time statistical anaylsis on this data. If they are stored, I don't have to recompute them again and again. I want to compute them once during write, then never again.

Collectives™ on Stack Overflow

"ambiguous column name" error from AFTER INSERT trigger

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related