给oracle表格添加主键

 

注意：

PLM_SPAREPARTS_DESDOC_RELATION为数据库表名
TASKID 为新加的一个字段
SEQ_PLM_SPA_DES_RELA_TASKID 为序列名
TRG_PLM_SPA_DESDOC_RELATION为触发器名


 1 --第0步
 2 alter table PLM_SPAREPARTS_DESDOC_RELATION add TASKID number
 3 
 4 --第1步
 5 create sequence SEQ_PLM_SPA_DES_RELA_TASKID INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE
 6 
 7 
 8 --第2步
 9 create or replace trigger TRG_PLM_SPA_DESDOC_RELATION
10   before insert
11   on PLM_SPAREPARTS_DESDOC_RELATION
12   for each row
13 declare
14 
15  nextid number;
16 begin
17   IF :new.TASKID IS NULL then
18       select SEQ_PLM_SPA_DES_RELA_TASKID.NEXTVAL
19       into nextid  from dual;
20       :new.TASKID:=nextid;
21   end IF;
22 
23 
24 end TRG_PLM_SPA_DESDOC_RELATION;
25 
26 --第3步
27 update PLM_SPAREPARTS_DESDOC_RELATION set PLM_SPAREPARTS_DESDOC_RELATION.TASKID = SEQ_PLM_SPA_DES_RELA_TASKID.nextval;

 

posted @ 2020-08-14 07:58  不夹心饼干  阅读(1112)  评论(0)  编辑  收藏  举报