创建自动编号的 Oracle 触发器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8330305/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle trigger to create an autonumber
提问by Mike
I have never created a trigger in Oracle before so I am looking for some direction.
我之前从未在 Oracle 中创建过触发器，所以我正在寻找一些方向。
I would like to create a trigger that increments an ID by one if the ID isnt in the insert statement.
如果 ID 不在插入语句中，我想创建一个将 ID 增加 1 的触发器。
The ID should start at 10000, and when a record is inserted the next ID should be 10001. If the insert statement contains a ID, it should override the auto increment.
ID 应该从 10000 开始，当插入一条记录时，下一个 ID 应该是 10001。如果插入语句包含一个 ID，它应该覆盖自动增量。
ie
IE
insert into t1 (firstname, lastname) values ('Michael','Jordan'),('Larry','Bird')
should look like:
应该看起来像：
firstname lastname id
名字 姓氏 id
Micahel Jordan 10000
迈克尔乔丹 10000
Larry Bird 10001
拉里·伯德 10001
insert into t1 (firstname, lastname, id) values ('Scottie','Pippen',50000)
should look like:
应该看起来像：
firstname lastname id
名字 姓氏 id
Micahel Jordan 10000
迈克尔乔丹 10000
Larry Bird 10001
拉里·伯德 10001
Scottie Pippen 50000
斯科蒂皮蓬 50000
回答by Justin Cave
Something like this will work on 11g
像这样的东西将适用于 11g
CREATE SEQUENCE t1_id_seq
start with 10000
increment by 1;
CREATE TRIGGER trigger_name
BEFORE INSERT ON t1
FOR EACH ROW
DECLARE
BEGIN
IF( :new.id IS NULL )
THEN
:new.id := t1_id_seq.nextval;
END IF;
END;
If you're on an earlier version, you'll need to do a SELECT INTO to get the next value from the sequence
如果您使用的是早期版本，则需要执行 SELECT INTO 以从序列中获取下一个值
CREATE TRIGGER trigger_name
BEFORE INSERT ON t1
FOR EACH ROW
DECLARE
BEGIN
IF( :new.id IS NULL )
THEN
SELECT t1_id_seq.nextval
INTO :new.id
FROM dual;
END IF;
END;
Be aware that Oracle sequences are not gap-free. So it is entirely possible that particular values will be skipped for a variety of reasons. Your first insert may have an ID of 10000 and the second may have an ID of 10020 if it's done minutes, hours, or days later.
请注意，Oracle 序列不是无间隙的。因此，完全有可能出于各种原因跳过特定值。您的第一个插入的 ID 可能为 10000，如果在几分钟、几小时或几天后完成，第二个插入的 ID 可能为 10020。
Additionally, be aware that Oracle does not support specifying multiple rows in the VALUES clause as MySQL does. So rather than
此外，请注意 Oracle 不支持像 MySQL 那样在 VALUES 子句中指定多行。所以而不是
insert into t1 (firstname, lastname) values ('Michael','Jordan'),('Larry','Bird')
you'd need two separate INSERT statements
你需要两个单独的 INSERT 语句
insert into t1 (firstname, lastname) values ('Michael','Jordan');
insert into t1 (firstname, lastname) values ('Larry','Bird');
回答by Sebastian Brandner
I would recommend to code this trigger with a condition on the trigger itself, not in the sql block.
我建议使用触发器本身的条件编写此触发器，而不是在 sql 块中。
CREATE OR REPLACE TRIGGER your_trigger
BEFORE INSERT ON your_table
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
WHEN (new.id IS NULL)
BEGIN
SELECT your_sequence.nextval
INTO :new.id
FROM dual;
END;
/
With this solution the trigger is only executed if the condition matches (id is null).
使用此解决方案，触发器仅在条件匹配（id 为空）时才执行。
Otherwise the trigger is always executed and the block checks if id is null. The DB must execute the SQL block which does nothing on not null values.
否则总是执行触发器并且块检查 id 是否为空。DB 必须执行对非空值不执行任何操作的 SQL 块。
