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>
