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',
?? '');