如果没有表可以先创建个表
- ###建表###
- 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字段是主键的话
可以建一个主键保护器
- ###建保护PRIMARYKEY的触发器###
- 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;