将序列设置为字段的默认值 - oracle

时间:2023-03-08 16:01:06

1、表结构中log_id 非空、且为主键字段

create table TL_M_QRTZ_LOG
(
log_id NUMBER(8) not null,
job_id NUMBER(8) not null,
seq_no NUMBER(4),
start_time DATE not null,
end_time DATE,
exec_state VARCHAR2(256) default '0',
expr_eval VARCHAR2(256),
fail_reason VARCHAR2(1024)
)

2、建立触发器

create or replace trigger log_trigger before
insert on TL_M_QRTZ_LOG for each row
when (new.LOG_ID is null)
begin
select qrtz_sequence.nextval into:new.LOG_ID from dual;
end;

3、数据插入语句

INSERT INTO TL_M_QRTZ_LOG (
JOB_ID,
SEQ_NO,
START_TIME,
END_TIME,
EXEC_STATE,
FAIL_REASON)
VALUES (
#jobId#,
#seqNo#,
#startTime#,
#endTime#,
#execState#,
#failReason#)

通过2可以实现,为将序列作为默认值添加到字段上