Sequence Trigger

SQL> SQL> create sequence sq1  2  start with 1  3  increment by 1  4  minvalue 1  5  maxvalue 9999999  6  nocycle  7  nocache  8  noorder; Sequence created SQL> SQL> create or replace trigger pn_trigger  2  before insert on users  3  for each row  4  begin  5      select sq1.nextval into:new.id from sys.dual;  6  end;  7  / Trigger created SQL> insert into users( name) values('zhsan'); 1 row inserted SQL> select * from users;      ID NAME------- ------------------------------      1 zhsan SQL> insert into users values('lisi'); insert into users values('lisi') ORA-00947: not enough values SQL> insert into users( name) values('lisi'); 1 row inserted SQL> select * from users;      ID NAME------- ------------------------------      1 zhsan      2 lisi SQL> drop table users; Table dropped SQL> SQL> create table 表名(  2             userid number(10) primary key,  3             username varchar2(20)  4             ); Table created SQL> select * from 表名  2  /      USERID USERNAME----------- -------------------- SQL> SQL> CREATE SEQUENCE 序列名  2   INCREMENT BY 1   -- 每次加几个  3       START WITH 1     -- 从1开始计数  4       NOMAXVALUE       -- 不设置最大值  5       NOCYCLE          -- 一直累加，不循环  6       CACHE 10; Sequence created SQL> SQL> CREATE TRIGGER 触发器名 BEFORE  2  insert ON 表名 FOR EACH ROW  3  begin  4  select 序列名.nextval into:New.userid from dual;  5  end;  6  / Trigger created SQL> commit; Commit complete SQL> insert into 表名(Username) values('test'); 1 row inserted SQL> insert into 表名(Username) values('test'); 1 row inserted SQL> insert into 表名(Username) values('test'); 1 row inserted SQL> select * from 表名  2  /      USERID USERNAME----------- --------------------          1 test          2 test          3 test SQL> insert into 表名  values('test'); insert into 表名  values('test') ORA-00947: not enough values SQL> select * from 表名  2  /      USERID USERNAME----------- --------------------          1 test          2 test          3 test SQL> SQL> create sequence SEQ_TEST  2  increment by 1  3  start with 1  4  minvalue 1  5  nomaxvalue  6  nocycle  7  / Sequence created SQL> create table TEST(TEST_ID number primary key,symbol varchar2(10))  2  / Table created SQL> SQL> create trigger TRG_TEST before insert on TEST  2  for each row  3  begin  4  select SEQ_TEST.nextval into :new.TEST_ID from dual;  5  end;  6  / Trigger created SQL> insert into TEST(symbol) values('abc'); 1 row inserted SQL> insert into TEST(symbol) values('abc'); 1 row inserted SQL> select * from Test  2  /    TEST_ID SYMBOL---------- ----------         1 abc         2 abc SQL> insert into TEST values(SEQ_TEST.nextval,'bcd'); 1 row inserted SQL> select * from Test  2  /    TEST_ID SYMBOL---------- ----------         1 abc         2 abc         4 bcd SQL> insert into TEST values(SEQ_TEST.nextval,'bcd'); 1 row inserted SQL> select * from Test  2  /    TEST_ID SYMBOL---------- ----------         1 abc         2 abc         4 bcd         6 bcd SQL> insert into TEST values(SEQ_TEST.nextval,'bcd'); 1 row inserted SQL> select * from Test;    TEST_ID SYMBOL---------- ----------         1 abc         2 abc         4 bcd         6 bcd         8 bcd SQL> insert into TEST(symbol) values('abc'); 1 row inserted SQL> select * from Test;    TEST_ID SYMBOL---------- ----------         1 abc         2 abc         4 bcd         6 bcd         8 bcd         9 abc 6 rows selected  triSQL> drop  trigger TRG_TEST; Trigger dropped SQL> insert into TEST(symbol) values('abc'); insert into TEST(symbol) values('abc') ORA-01400: cannot insert NULL into ("SCOTT"."TEST"."TEST_ID") SQL> insert into TEST values(SEQ_TEST.nextval,'bcd'); 1 row inserted SQL> insert into TEST values(SEQ_TEST.nextval,'bcd'); 1 row inserted SQL> insert into TEST values(SEQ_TEST.nextval,'bcd'); 1 row inserted SQL> insert into TEST values(SEQ_TEST.nextval,'bcd'); 1 row inserted SQL> select * from Test;    TEST_ID SYMBOL---------- ----------         1 abc         2 abc         4 bcd         6 bcd         8 bcd         9 abc        10 bcd        11 bcd        12 bcd        13 bcd 10 rows selected SQL>