Ошибка «неоднозначное имя столбца» из триггера AFTER INSERT

В версии DBeaver 23.3.4.202402060628 у меня есть следующий код SQLite3 (для высокоскоростной машины для литья под давлением деталей и машины для отслеживания упаковок на заводе):

--------------------------------------------------------------------------------
-- 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 *;

У меня был рабочий код, но теперь я получаю сообщение об ошибке, когда дохожу до оператора INSERT (DBeaver принимает каждый блок):

Ошибка SQL 1: [SQLITE_ERROR] Ошибка SQL или отсутствующая база данных (неоднозначное имя столбца: Вес)

Существует только одна таблица с одним столбцом с именем Weight. Из этого поста я подумал, что что-то еще есть в памяти, поэтому отключил все остальные базы данных, удалил все таблицы из этой и начал с нуля. Я попытался использовать полные имена, добавив имя таблицы:

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

Но затем выдает синтаксическую ошибку для точки.

Хорошо, я обновился и создал таблицу и индексы, но не триггер, и INSERT сработал. Но мне нужен триггер для вычисляемых столбцов — мне нужно, чтобы вычисляемые столбцы вычислялись при вставке каждой строки. Это данные временных рядов, и это очень важно.

skeetastax 12.07.2024 12:27

Итак, теперь вы знаете, что проблема в триггере. Затем начните отладку обновления в нем как отдельный запрос (используя фиктивные значения для полей старой строки).

Shawn 12.07.2024 20:19

Вот это в скрипте БД: dbfiddle.uk/SSk1oWVf. Он принимает синтаксис триггера. Я не совсем уверен, как это отладить. Мне помогли его построить (из другого моего поста). Не могли бы вы объяснить, что вы подразумеваете под «отладкой обновления как отдельного запроса»?

skeetastax 12.07.2024 23:44
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
0
3
87
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

«… но теперь я получаю сообщение об ошибке, когда дохожу до оператора INSERT…»

Триггер AFTER INSERT в своем событии UPDATE не может различить вставленную (до события триггера) и обновленную (после события триггера) связанную строку во всех именах столбцов каждого предложения WHEN в каждом из трех выражений CASE, отсюда и ошибка «неоднозначное имя столбца».

«DBeaver принимает каждый блок»

Это не приводит к синтаксической ошибке при выдаче CREATE TRIGGER, поскольку само выражение CASE допустимо (только в контексте триггерного события оно становится неоднозначным).

«Я пытался использовать полные имена, добавляя к началу имя таблицы: … Но затем в точке выдается синтаксическая ошибка».

Это ожидаемо.

Добавьте в начало имена столбцов с предполагаемой ссылкой на событие триггера до или после (NEW.column-name или OLD.column-name) в каждом предложении WHEN в каждом из выражений UPDATE события триггера CASE. Кроме того, рассмотрите возможность использования виртуальных столбцов (или вычислений, которые их генерируют) в предложениях CASE выражений WHEN, а не отдельно SET этих данных в одном и том же триггерном UPDATE событии, вычисления которого от него зависят (избыточно дублируясь в процессе).

Исправленное утверждение TRIGGER:

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;

Исправлено dbfiddle.

Судя по тому, что я читал, виртуальные столбцы генерируются только в памяти и возвращаются в качестве результатов запроса — я хочу, чтобы они постоянно хранились в таблице, поскольку они никогда не изменятся. Я знаю, что это означает больше дискового пространства для хранения БД. Я планирую провести статистический анализ этих данных в режиме реального времени. Если они сохранены, мне не придется пересчитывать их снова и снова. Я хочу вычислить их один раз во время записи, а затем больше никогда.

skeetastax 14.07.2024 03:02

Другие вопросы по теме