创建自动编号的 Oracle 触发器

Question

我之前从未在 Oracle 中创建过触发器，所以我正在寻找一些方向。

如果 ID 不在插入语句中，我想创建一个将 ID 增加 1 的触发器。

ID 应该从 10000 开始，当插入一条记录时，下一个 ID 应该是 10001。如果插入语句包含一个 ID，它应该覆盖自动增量。

即

insert into t1 (firstname, lastname) values ('Michael','Jordan'),('Larry','Bird')

应该看起来像:

名字 姓氏 id

迈克尔乔丹 10000

拉里·伯德 10001

insert into t1 (firstname, lastname, id) values ('Scottie','Pippen',50000)

应该看起来像:

名字 姓氏 id

迈克尔乔丹 10000

拉里·伯德 10001

斯科蒂皮蓬 50000

Answer 1

像这样的东西将适用于 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;

如果您使用的是早期版本，则需要执行 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;

请注意，Oracle 序列不是无间隙的。因此，完全有可能出于各种原因跳过特定值。您的第一个插入的 ID 可能为 10000，如果在几分钟、几小时或几天后完成，第二个插入的 ID 可能为 10020。

此外，请注意 Oracle 不支持像 MySQL 那样在 VALUES 子句中指定多行。所以而不是

insert into t1 (firstname, lastname) values ('Michael','Jordan'),('Larry','Bird')

你需要两个单独的 INSERT 语句

insert into t1 (firstname, lastname) values ('Michael','Jordan');
insert into t1 (firstname, lastname) values ('Larry','Bird');