使用触发器自动递增

发布于 2024-08-24 16:19:59 字数 406 浏览 4 评论 0原文

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

我不知道这是否可能，但我试图将最后使用的 id 存储在不同的表中，并在插入新记录时使用触发器分配下一个 id。

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

如何根据不同表中的值设置要插入的行的字段

到目前为止的查询是：

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

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

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

樱＆纷飞 2024-08-31 16:19:59

如果您使用 AFTER INSERT 触发器，则可以更新新插入的行，如下例所示。

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;

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;

回复收藏 0 原文

~没有更多了~