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
  tri
SQL> 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>