±±oracle trigger±±

在ORACLE 內部建立觸發器的時候，不能在建立的的基表內引用其他資料，而在建立觸發器的往往需要引用基表的資料，這時候我感覺最好的辦法上：建立檢視！！！！！！！

-- Create table
create table AJCJ_ZAAJDJB
(
BH VARCHAR2(30) not null,
YEAR VARCHAR2(4),
YEAR_NO VARCHAR2(20),
MONTH_NO VARCHAR2(22),
AB VARCHAR2(30),
FA_TIME DATE,
FADD VARCHAR2(30),
BASJ DATE,
DSR1_XM VARCHAR2(30),
DSR1_XB VARCHAR2(30),
DSR1_AGE VARCHAR2(30),
DSR1_ZZ VARCHAR2(30),
DSR1_GGDW VARCHAR2(30),
DSR2_XM VARCHAR2(30),
DSR2_XB VARCHAR2(30),
DSR2_AGE VARCHAR2(30),
DSR2_ZZ VARCHAR2(30),
DSR2_GGDW VARCHAR2(30),
AJQK VARCHAR2(100),
DCJJ VARCHAR2(100),
CLYJ_NR VARCHAR2(50),
CLYJ_TIME_YEAR VARCHAR2(4),
JBR VARCHAR2(30),
DSR1_SFZ VARCHAR2(30),
DSR2_SFZ VARCHAR2(30),
LRRY VARCHAR2(14),
LRDW VARCHAR2(12),
LRSJ DATE,
BDRY VARCHAR2(14),
BDDW VARCHAR2(12),
BDSJ DATE,
XGBS VARCHAR2(2),
JY VARCHAR2(30),
JYBM VARCHAR2(14),
PCS VARCHAR2(14),
CLYJ_TIME_MONTH VARCHAR2(2),
CLYJ_TIME_DAY VARCHAR2(2),
CLYJ_TIME VARCHAR2(30),
BADD DATE
)

create or replace trigger tr_ajcj_zaajdjb_001
before insert on ajcj_zaajdjb
for each row
declare
-- local variables here
year_no number;
month_no number;
begin
select year_nos,month_nos into year_no,month_no
from view_tr_ajcj_zaajdjb_001;-- where substrb(sj,1,4)
=to_char(sysdate,'yyyy');
:new.YEAR_NO:=substrb(:new.jybm,1,12)||year_no;
:new.MONTH_NO:=substrb(:new.jybm,1,12)||month_no;
end tr_ajcj_zaajdjb_001;

create or replace view view_tr_ajcj_zaajdjb_001 as
select a.year_nos,b.month_nos
from
(select nvl(max(substrb(year_no,13,8))+1,
to_char(sysdate,'yyyy')||'0001') year_nos from ajcj_zaajdjb
where substrb(year_no,13,4)=to_char(sysdate,'yyyy')) a,
(select nvl(max(substrb(month_no,13,10))+1,
to_char(sysdate,'yyyymm')||'0001') month_nos from ajcj_zaajdjb
where substrb(month_no,13,6)=to_char(sysdate,'yyyymm')) b

[@more@]