Use trigger for auto-increment

Question

I'm trying to solve the problem that composite keys in sqlite don't allow autoincrement.

I don't know if it's possible at all, but I was trying to store the last used id in a different table, and use a trigger to assign the next id when inserting a new reccord.

I have to use composite keys, because a single pk wouldn't be unique (because of database merging).

How can I set a field of the row being inserted based on a value in a different table

The query so far is:

CREATE TRIGGER pk BEFORE INSERT ON product_order
BEGIN
    UPDATE auto_increment SET value = value + 1 WHERE `table_name` = "product_order";
END

This successfully updates the value. But now I need to assign that new value to the new record. (new.id).

Answer 1

If you use an AFTER INSERT trigger then you can update the newly inserted row, as in the following example.

CREATE TABLE auto_increment (value INT, table_name TEXT);
INSERT INTO auto_increment VALUES (0, 'product_order');

CREATE TABLE product_order (ID1 INT, ID2 INT, name TEXT);

CREATE TRIGGER pk AFTER INSERT ON product_order
BEGIN

    UPDATE  auto_increment 
    SET     value = value + 1 
    WHERE   table_name = 'product_order';

    UPDATE  product_order 
    SET     ID2 = (
                SELECT value 
                FROM auto_increment 
                WHERE table_name = 'product_order')
    WHERE   ROWID = new.ROWID;
END;

INSERT INTO product_order VALUES (1, NULL, 'a');
INSERT INTO product_order VALUES (2, NULL, 'b');
INSERT INTO product_order VALUES (3, NULL, 'c');
INSERT INTO product_order VALUES (4, NULL, 'd');

SELECT * FROM product_order;