Oracle 实现自动递增的功能

模仿

 1 create sequence SEQ_ID
 2 minvalue 1
 3 maxvalue 99999999
 4 start with 1
 5 increment by 1
 6 nocache
 7 order;
 8  
 9 创建触发器：
10  
11 create trigger tri_test_id    --（1）
12 before insert on test for each row  --test 是表名
13 begin
14 select SEQ_ID.nextval into :new.id from dual;
15 end tri_test_id;
16  
17 create or replace trigger tri_test_id    --（2）
18   before insert on S_Depart   --S_Depart 是表名
19   for each row
20 declare
21   nextid number;
22 begin
23   IF :new.DepartId IS NULL or :new.DepartId=0 THEN --DepartId是列名
24     select SEQ_ID.nextval --SEQ_ID正是刚才创建的
25     into nextid
26     from sys.dual;
27     :new.DepartId:=nextid;
28   end if;
29 end tri_test_id;

 

实例

 1 CREATE SEQUENCE rsk_book_filter_seq
 2 INCREMENT BY 1 
 3 START WITH 10000 
 4 NOMAXVALUE 
 5 NOCYCLE 
 6 NOCACHE 
 7 
 8 CREATE OR REPLACE TRIGGER rsk_book_filter_tri
 9 BEFORE INSERT ON rsk_book_filter
10 FOR EACH ROW
11 BEGIN
12 SELECT rsk_book_filter_seq.NEXTVAL INTO :NEW.RISK_BOOK_FILTER_ID FROM DUAL;
13 END;

 

posted @ 2015-11-19 16:06  Goosander  阅读(833)  评论(0)  编辑  收藏  举报