SQLite插入时生成YEAR+COUNT组合主键触发器报错排查

我需要为Products表生成格式为「年份+10位计数器」的唯一主键ID，表结构如下：

CREATE TABLE IF NOT EXISTS YearCounters (
    year INTEGER PRIMARY KEY,
    counter INTEGER DEFAULT 0
);

-- Product Table
CREATE TABLE IF NOT EXISTS Products (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL
);

插入示例数据：

INSERT INTO Products (name, price) VALUES ('Produit B', 20.15);
INSERT INTO Products (name, price) VALUES ('Produit C', 23.75);

期望得到的结果：

SELECT * FROM Products;

2024000000000001|Produit B|20.15
2024000000000002|Produit C|23.75

我编写的触发器无法运行：

CREATE TRIGGER IF NOT EXISTS increment_counter_and_set_product_id
BEFORE INSERT ON Products
FOR EACH ROW
BEGIN
    -- 
    UPDATE YearCounters
    SET counter = counter + 1
    WHERE year = strftime('%Y', 'now');

    --
    INSERT OR IGNORE INTO YearCounters (year, counter)
    VALUES (strftime('%Y', 'now'), 0);

    --
    SELECT counter
    INTO temp_counter
    FROM YearCounters
    WHERE year = strftime('%Y', 'now');

    -- 
    SET NEW.id = strftime('%Y', 'now') || printf('%010d', temp_counter);
END;

报错信息：

Parse error near line near "INTO": syntax error
SELECT counter INTO temp_counter FROM Y
error here ---^

错误原因 #

SQLite的触发器语法不支持SELECT ... INTO这种声明并赋值临时变量的写法（这是MySQL等其他数据库的语法），同时原触发器中UPDATE和INSERT OR IGNORE的顺序逻辑错误：如果当前年份没有计数器记录，先执行UPDATE不会生效，后续插入初始值后计数器未被正确递增。

修正后的触发器代码 #

CREATE TRIGGER IF NOT EXISTS increment_counter_and_set_product_id
BEFORE INSERT ON Products
FOR EACH ROW
BEGIN
    -- 先确保当前年份的计数器记录存在，不存在则插入初始值0
    INSERT OR IGNORE INTO YearCounters (year, counter)
    VALUES (strftime('%Y', 'now'), 0);

    -- 递增当前年份的计数器
    UPDATE YearCounters
    SET counter = counter + 1
    WHERE year = strftime('%Y', 'now');

    -- 直接通过子查询获取计数器值，拼接生成ID
    SET NEW.id = (
        SELECT strftime('%Y', 'now') || printf('%010d', counter)
        FROM YearCounters
        WHERE year = strftime('%Y', 'now')
    );
END;

说明 #

1. 调整操作顺序：先确保当前年份的计数器记录存在，再执行递增操作，保证每次插入都能正确更新计数器。
2. 移除临时变量：直接在SET NEW.id语句中通过子查询获取最新的计数器值，避免SQLite不支持的语法。

内容的提问来源于stack exchange，提问作者kamel Derouiche