使用触发器进行自动增量 [英] Use trigger for auto-increment

问题描述

我正在尝试解决sqlite中的组合键不允许自动递增的问题。

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

我不知道是否有可能，但是我试图将上次使用的ID存储在另一个表中，并在插入新的记录时使用触发器分配下一个ID。

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.

我必须使用复合键，因为单个pk不会唯一（因为数据库合并）。

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

到目前为止的查询是：

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

这将成功更新值。但是现在我需要将该新值分配给新记录。 （new.id）。

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

推荐答案

如果使用之后插入触发，然后您可以更新新插入的行，如下例所示。

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;

这篇关于使用触发器进行自动增量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！