Oracle DB 数据准备

Create Table

create table DEP (  
  deptno        number,  
  dname        varchar2(50) not null,  
  location      varchar2(50),  
  constraint pk_departments primary key (deptno)  
);

create table EMP (  
  empno             number,  
  ename              varchar2(50) not null,  
  job               varchar2(50),  
  manager           number,  
  hiredate          date,  
  salary            number(7,2),  
  commission        number(7,2),  
  deptno           number,  
  constraint pk_employees primary key (empno),  
  constraint fk_employees_deptno foreign key (deptno) 
      references DEP (deptno)  
);

Create / Replace the Trigger

方法一：

# 从一开始，创建一个序列
CREATE SEQUENCE zero_based_seq
START WITH 1
INCREMENT BY 1;

#创建一个触发器，每增加一行新数据，primary key 加1
create or replace trigger  DEP_BIU
    before insert or update on DEP
    for each row
begin
    SELECT zero_based_seq.NEXTVAL INTO : NEW.deptno FROM DUAL;
end;

create or replace trigger  EMP_BIU
    before insert or update on EMP
    for each row
begin
    SELECT zero_based_seq.NEXTVAL INTO : NEW.empno FROM DUAL;
end;

方法二：

create or replace trigger  DEP_BIU
    before insert or update on DEP
    for each row
begin
    if inserting and :new.deptno is null then
        :new.deptno := to_number(sys_guid(), 
          'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
    end if;
end;
/

create or replace trigger EMP_BIU
    before insert or update on EMP
    for each row
begin
    if inserting and :new.empno is null then
        :new.empno := to_number(sys_guid(), 
            'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
    end if;
end;