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