回滚顺序
我有一个表，其中使用序列填充主键。当前版本：
CREATE SEQUENCE xxxx.SEQ_xyz
START WITH 135
MAXVALUE 999999999999999999999999999
MINVALUE 1
NOCYCLE
NOCACHE
ORDER;
有一个应用程序可以从表中删除一些行。发生这种情况时，我想获取下一个可用的数字，而不是序列显示的数字。假设我插入： 136× 137岁 138 z
因此序列现在位于 139。如果我删除 136、137、138，我希望序列返回到 136，而不是停留在 139。这可能吗？
提前致谢。
I have a table where the primary key is populated using a sequence. Current version:
CREATE SEQUENCE xxxx.SEQ_xyz
START WITH 135
MAXVALUE 999999999999999999999999999
MINVALUE 1
NOCYCLE
NOCACHE
ORDER;
There is an application which deletes some rows from a table. When this happens, I want to get the next available number instead of what sequence shows. Say I insert:
136 x
137 y
138 z
so the sequence is now at 139. if i delete 136, 137, 138, I want sequence to go back to 136 instead of staying at 139. Is this possible?
thanks in advance.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您的问题的答案是，在每行触发器上写一个删除前的操作，该触发器将用要删除的条目的“开头”替换序列。但这会产生很多问题，正如 Shannon Severance 所提到的。
例如，136 被删除，触发器替换旧序列，现在序列以 136 开头。您将能够插入主键为 136 的内容，但下次您尝试在表中插入数据时，您将无法插入数据能够插入它，因为它将违反主键约束（表中将有 137, 138）
我看不出有任何理由要返回到之前删除的号码。按照你的顺序。你能详细说明一下吗？
The answer to your question is, write a before delete on each row trigger which would replace the sequence with "start with" the entry which is going to be deleted. But this will create lot of problems as mentioned by Shannon Severance.
e.g 136 gets deleted, the trigger replaces the old sequence, now the sequence starts with 136. you will be able to insert something with primary key as 136 but the next time you'll try to insert data in the table you won't be able to insert it as it will violate the primary key constraint (137, 138 would be there in the table)
I don't see any reason why you what to go back to the previously deleted no. in your sequence. Can you elaborate on that?