Create composed key (YEAR+COUNT) field with trigger after insert in SQLITE

Question

I have a table Product with two columns: name of product and price

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

My objectif,Creates a unique key in the Product table, which must be composed of the year plus a number of size 10 which increments for each new product added YEAR+COUNT(10) :

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

The product table should look like this

Example:

SELECT * FROM Products;
2024000000000001|Produit B|20.15
2024000000000002|Produit C|23.75

I've developed a TRIGGER for the primary key, but it doesn't work. .

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;

Error: I recieve the error

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

What am I doing wrong?

• Community: Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.

Answer 1

You cannot select into a variable, and also you cannot SET NEW.id in the trigger outside of an UPDATE statement. You can only have SELECT, INSERT, UPDATE or DELETE statements in the trigger.

CREATE TRIGGER IF NOT EXISTS increment_counter_and_set_product_id
AFTER INSERT ON Products
FOR EACH ROW
BEGIN
 INSERT OR IGNORE INTO YearCounters (year, counter)
 VALUES (strftime('%Y', 'now'), 0);
 -- 
 UPDATE YearCounters
 SET counter = counter + 1
 WHERE year = strftime('%Y', 'now');
 -- 
 UPDATE Products
 SET id = (SELECT strftime('%Y', 'now') || printf('%010d', counter) FROM YearCounters WHERE year = strftime('%Y', 'now'))
 WHERE rowid=NEW.rowid;
END;

I put the insert before the update, so the numbers starts with 1 like in your example, and not 0. The id can be set with an UPDATE, but you need to know the rowid of the inserted Product, which is undefined in a BEFORE INSERT trigger, so you need an AFTER INSERT trigger instead.

The above trigger will always generate new numbers, even if some products are deleted after insert, without reusing any of them. If products are never deleted, or you can reuse the last number in case the last product is deleted, you can simplify the trigger and drop the YearCounters table, getting the last number straight from the Products table:

CREATE TRIGGER IF NOT EXISTS increment_counter_and_set_product_id
AFTER INSERT ON Products
FOR EACH ROW
BEGIN
 UPDATE Products
 SET id = 
 (SELECT strftime('%Y', 'now') || printf('%010d', ifnull(max(cast(substr(id,5) as integer)),0)+1) 
 FROM Products 
 WHERE substr(id,1,4) = strftime('%Y', 'now'))
 WHERE rowid=NEW.rowid;
END;