oracle设置自增字段，oracle序列

如果没有表可以先创建个表

###建表###

CREATE TABLE "NWVIDEO"."DEVICE"
(
"DID" NUMBER NOT NULL,
"TYPE" NUMBER(3) NOT NULL,
"BODY" VARCHAR2(100) NOT NULL, 
"HITS" NUMBER(10) DEFAULT 0 NOT NULL, 
PRIMARY KEY("ID")
)

 然后是设置序列

###创建序列### 

CREATE SEQUENCE "NWVIDEO"."NWVIDEO_DEVICE_DID_SEQ" 
INCREMENT BY 1     --每次加1个
START WITH 1           --从1开始计数
NOMAXVALUE            --不设置最大值
MINVALUE 1                --最小值为1
NOCYCLE                    --一直累加，不循环
CACHE 50                   --设置缓存为50

 建立触发器

###建自动更新的触发器###

CREATE OR REPLACE TRIGGER "NWVIDEO"."NWVIDEO_DEVICE_DID_TRIGGER"
BEFORE INSERT
ON "NWVIDEO"."DEVICE"
FOR EACH ROW
DECLARE
next_did NUMBER;
BEGIN
--Get the next id number from the sequence
SELECT NWVIDEO_device_did_seq.NEXTVAL
INTO next_did
FROM dual;
--Use the sequence number as the primary key
--for the record being inserted.
:new.did := next_did;
END;

 如果did字段是主键的话

可以建一个主键保护器

###建保护PRIMARYKEY的触发器### 

CREATE OR REPLACE TRIGGER "TEST"."NWVIDEO_DID_UPDATE_TRIGGER"
BEFORE UPDATE OF "DID" ON "NWVIDEO"."DEVICE"
FOR EACH ROW
BEGIN
RAISE_APPLICATION_ERROR (-20000,'vwvideo_device_did_update_trigger:Updates of the DID field'||'are not allowed.'); 
END;

 

黑色头发  http://heisetoufa.iteye.com