建表 自增sequence

原创
2014/02/10 13:48
阅读数 175

-- Create table

create table T_CI_UPLOAD_LOG

(

  LOG_ID      NUMBER not null,

  ERR_CODE    NUMBER,

  TRANS_ID    NUMBER,

  DETAIL_ID   NUMBER,

  ERR_EXT     VARCHAR2(2000),

  INSERT_TIME DATE,

  LOG_LEVEL   NUMBER

);

-- Create/Recreate primary, unique and foreign key constraints 

alter table T_CI_UPLOAD_LOG

  add constraint PK_CI_UPLOAD_LOG_ID primary key (LOG_ID);

alter table T_CI_UPLOAD_LOG

  add constraint FK_CI_UPLOAD_ERR_CODE foreign key (ERR_CODE)

  references T_CI_UPLOAD_ERR_CODE (ERR_CODE);

alter table T_CI_UPLOAD_LOG

  add constraint FK_CI_UPLOAD_LOG_DETAIL_ID foreign key (DETAIL_ID)

  references T_CI_UPLOAD_FILE_DETAIL (DETAIL_ID);

alter table T_CI_UPLOAD_LOG

  add constraint FK_CI_UPLOAD_LOG_TRANS_ID foreign key (TRANS_ID)

  references T_CI_UPLOAD_FILE_SUMMARY (TRANS_ID);

  -- Create sequence 

create sequence S_CI_UPLOAD_LOG_ID

minvalue 1

maxvalue 999999999999999999999999999

start with 1

increment by 1;

---create trigger for table use sequence for id ++

create trigger "TCI_UPLOAD_FILE_LOG_TRIGGER" before

insert on t_ci_upload_log for each row 

begin

select S_CI_UPLOAD_LOG_ID.nextval into :new.log_id from dual;

end;


-----如果不加trigger 需要在 java 代码insert语句之前加一句

 jewelMpBillingAdjustInfoVO.setId(OracleSequencePKGenerator.getInstance()

          .getNextPKValue("S_MP_BILLING_ADJUST_ID"));

先查询sequence 的nextval 然后把值赋给对象 

再插入


展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
打赏
0 评论
1 收藏
0
分享
返回顶部
顶部