REM =======關務料件每日使用量信息表======
REM id //自動增1的編號
REM dzzc_id //電子賬冊號
REM gwdm_id //關務代碼
REM cpba_id //成品備案號
REM version_id //成品版本號
REM ljba_id //料件備案號
REM gwyl //關務用量
REM ch_date //出貨日期
REM 本表根據系統中的出貨計劃計算每日的每個料件的關務使用量
drop table gwyl;
create table gwyl(
id number(15,0) not null,
dzzc_id varchar2(15 byte) not null,
gwdm_id varchar2(6 byte) not null,
cpba_id number(4,0) not null,
version_id number(3,0) not null,
ljba_id number(4,0) not null,
gwyl number(15,3) not null,
ch_date date not null,
constraint gwyl_id_pk primary key(id) using index tablespace cpindex
)TABLESPACE "LOGISTIC" PCTFREE 10 PCTUSED 0 INITRANS 1
MAXTRANS 255 STORAGE (
INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0)
LOGGING ;
REM 創建一個自動增1的序列
drop sequence gwyl_id_seq;
create sequence gwyl_id_seq
nocycle
maxvalue 999999999999999
start with 1
order
increment by 1;
REM ==========創建執行自動增加主鍵字段的一個触發器=============
drop trigger gwyl_id_trigger;
create or replace trigger gwyl_id_trigger
before insert on gwyl
for each row
declare
next_id number;
begin
--get the next id from the sequence
select gwyl_id_seq.nextval
into next_id
from dual;
--use the sequence number as primary
--for the record begin inserted
:new.id:=next_id;
end ;
/
REM 創建一個防止ID字段被更新的触發器
drop trigger gwyl_id_upd;
create or replace trigger gwyl_id_upd
before update of id on gwyl
for each row
begin
raise_application_error(-2000,'update column id on table gwyl are not allowed.');
end;
/