首先,創建一張表:
create table test( id number(32) not null primary key, name varchar2(32) );
CREATE SEQUENCE test_sequence INCREMENT BY 1 -- 每次加幾個 START WITH 1 -- 從1開始計數 NOMAXVALUE -- 不設置最大值 NOCYCLE -- 一直累加,不循環 NOCACHE -- 不建緩沖區
CREATE TRIGGER test_trigger BEFORE INSERT ON test FOR EACH ROW WHEN (new.id is null) --只有在id為空時,啟動該觸發器生成id號 begin select test_sequence.nextval into :new.id from sys.dual; end;
insert into test(name) values('zhangsan'); insert into test(id, name) values(101,'lisi');
PS:創建trigger時into :new.id from sys.dual中的":"是靠近new的 而不是into: new.id from sys.dual