--drop table REPORT_LOG cascade constraints;

/*==============================================================*/
/* Table: REPORT_LOG                                            */
/*==============================================================*/
create table REPORT_LOG  (
   ID                   NUMBER(20)                      not null,
   OPR_NAME_CN          VARCHAR2(50),
   OPR_NAME_EN          VARCHAR2(50),
   OPR_TYPE             NUMBER(6)                      default 0,
   RUN_TIME             NUMBER(6),
   RUN_EXCEPTION        VARCHAR2(3999),
   RUN_DATE             DATE,
   OPR_STATE            NUMBER(6)                      default 0,
   OPR_VALIDATE         CHAR(10)                       default '0',
   CREATED_BY           VARCHAR2(50),
   CREATED_DATE         DATE,
   UPDATED_BY           VARCHAR2(50),
   UPDATED_DATE         DATE,
   constraint PK_REPORT_LOG primary key (ID)
);

comment on table REPORT_LOG is
'操作日志';

comment on column REPORT_LOG.ID is
'主键 由触法器自动创建';

comment on column REPORT_LOG.OPR_NAME_CN is
'操作名称（中文）';

comment on column REPORT_LOG.OPR_NAME_EN is
'操作名称（英文）';

comment on column REPORT_LOG.OPR_TYPE is
'操作类型 0-手动，1-Quartz Job，2-Oracle Job';

comment on column REPORT_LOG.RUN_TIME is
'已运行次数';

comment on column REPORT_LOG.RUN_EXCEPTION is
'运行失败记录';

comment on column REPORT_LOG.RUN_DATE is
'截止时间点';

comment on column REPORT_LOG.OPR_STATE is
'操作状态 0-正在执行，1-成功，2-失败，3-等待执行，4-正在重试';

comment on column REPORT_LOG.OPR_VALIDATE is
'是否有效 0-有效，1-无效';

comment on column REPORT_LOG.CREATED_BY is
'创建人编码';

comment on column REPORT_LOG.CREATED_DATE is
'创建时间';

comment on column REPORT_LOG.UPDATED_BY is
'修改人编码';

comment on column REPORT_LOG.UPDATED_DATE is
'修改时间';



--drop sequence SEQ_REPORT_DATA_RES;

create sequence SEQ_REPORT_DATA_RES
increment by 1
start with 1
  maxvalue 999999999
  minvalue 1;

--drop trigger "tib_report_log"
/


create trigger "tib_report_log" before insert
on REPORT_LOG for each row
declare
    integrity_error  exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;

begin
    --  Column "ID" uses sequence SEQ_REPORT_DATA_RES
    select SEQ_REPORT_DATA_RES.NEXTVAL INTO :new.ID from dual;

--  Errors handling
exception
    when integrity_error then
       raise_application_error(errno, errmsg);
end;
/

?