创建sequence和TRIGGER实现ID自增长

CREATE TABLE s_if_erptransferlog
(
  JobID    VARCHAR2(200)  NOT NULL,
  startdatetime    TIMESTAMP  NOT NULL,
  enddatetime    TIMESTAMP  NOT NULL,
  usedtime    NUMBER(10, 0)  NOT NULL,
  processcount    NUMBER(22, 0)  NOT NULL,
  result    VARCHAR2(40)  NOT NULL,
  errormsg    VARCHAR2(400) ,
  serial    NUMBER(38, 0)  NOT NULL
)
;
COMMENT ON TABLE s_if_erptransferlog IS '记录每次处理的结果'
;
COMMENT ON COLUMN s_if_erptransferlog.JobID IS 'JOB唯一标识'
;
COMMENT ON COLUMN s_if_erptransferlog.startdatetime IS '开始时间'
;
COMMENT ON COLUMN s_if_erptransferlog.enddatetime IS '结束时间'
;
COMMENT ON COLUMN s_if_erptransferlog.usedtime IS '所用时间(毫秒)'
;
COMMENT ON COLUMN s_if_erptransferlog.processcount IS '处理资料比数，错误时默认为一笔。成功记录所以处理成功的笔数。'
;
COMMENT ON COLUMN s_if_erptransferlog.result IS '处理结果:Failed，Success。'
;
COMMENT ON COLUMN s_if_erptransferlog.errormsg IS '出错讯息'
;
COMMENT ON COLUMN s_if_erptransferlog.serial IS '自增长ID'
;

--创建sequence
create sequence s_if_erptransferlog_s
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
nocache;

--创建TRIGGER

CREATE OR REPLACE TRIGGER "s_if_erptransferlog_BRI1"
 BEFORE
  INSERT
 ON s_if_erptransferlog
REFERENCING NEW AS NEW OLD AS OLD
 FOR EACH ROW
BEGIN
   SELECT s_if_erptransferlog_s.nextval
     INTO :NEW.Serial
     FROM DUAL;
END;

 

--测试 

insert into s_if_erptransferlog
  (JobID,
   startdatetime,
   enddatetime,
   usedtime,
   processcount,
   result,
   errormsg
)
values
  ('insertJob',
   sysdate,
   sysdate,
   22222,
   5,
   'Success',
   '');