create or replace trigger TRG_ENTER_BLACK
after INSERT OR UPDATE OR DELETE on enter_black
for each row
declare
-- local variables here
V_enter_black enter_black%ROWTYPE;
v_operate VARCHAR2(1);
v_org NUMBER(12) := 1;
v_id NUMBER(12);
v_seq_id NUMBER(12) := 1;
v_create_org number(12) := 1;
v_law_dict_id number(8) := 105;
v_LOAD_MODE char(1) := 1;
v_load_status char(1) := 'A';
V_STATE char(1) := 'M';
v_date date;
v_caution_reason varchar2(1000);
V_enter_revdate varchar2(20);
v_illegact varchar2(200);
begin IF inserting THEN
---给V_enter_black 付值
v_operate := 'A';
v_enter_black.name :=:new.name;
v_enter_black.cerno :=:new.cerno;
v_enter_black.REVDATE:=:new.REVDATE;
v_ENTER_BLACK.entname:=:new.entname;
V_enter_black.regno :=:new.regno;
V_enter_black.ENTTYPE:=:new.enttype;
v_enter_black.illegact:=:new.illegact;
ELSIF updating THEN
---给V_enter_black 付值
v_operate := 'U';
v_enter_black.name :=:new.name;
v_enter_black.cerno :=:new.cerno;
v_enter_black.REVDATE:=:new.REVDATE;
v_ENTER_BLACK.entname:=:new.entname;
V_enter_black.regno :=:new.regno;
V_enter_black.ENTTYPE:=:new.enttype;
v_enter_black.illegact:=:new.illegact;
END IF;
IF v_operate = 'A' THEN
v_caution_reason :='担任'||v_enter_black.entname||'的法定代表人企业因为'||v_illegact||'在'||V_enter_revdate||'被'||v_ENTER_BLACK.REGORG||'吊销';
select to_char(v_ENTER_BLACK.REVDATE,'yyyy-mm-dd') into V_enter_revdate from ENTER_BLACK;
SELECT s_name into v_illegact from t_dict_ce02 where s_code=v_enter_black.illegact;
SELECT id INTO v_id FROM t_id_compare WHERE S_EXT_NODENUM = V_enter_black.S_EXT_NODENUM AND pripid = V_enter_black.Pripid AND table_id = 1 AND table_kind = 1;
select add_months (v_enter_black.REVDATE,36)-1 into v_date from enter_black;
case
when V_enter_black.ENTTYPE between 1000 and 1099 then
INSERT INTO t_law_caution VAlUES(v_org,v_id,v_seq_id,v_CREATE_ORG,v_enter_black.name,v_enter_black.cerno,v_law_dict_id,v_enter_black.REVDATE,v_date,v_LOAD_MODE,
v_load_status,v_enter_black.REVDATE,
v_enter_black.REVDATE,V_STATE,null,v_caution_reason,null,null,null,null,V_enter_black.regno,v_ENTER_BLACK.entname,null,null,null,null);
when V_enter_black.ENTTYPE between 3000 and 3999 then
INSERT INTO t_law_caution VAlUES(v_org,v_id,v_seq_id,v_CREATE_ORG,v_enter_black.name,v_enter_black.cerno,v_law_dict_id,v_enter_black.REVDATE,v_date,v_LOAD_MODE,
v_load_status,v_enter_black.REVDATE,
v_enter_black.REVDATE,V_STATE,null,v_caution_reason,null,null,null,null,V_enter_black.regno,v_ENTER_BLACK.entname,null,null,null,null);
when V_enter_black.ENTTYPE between 5000 and 5399 then
INSERT INTO t_law_caution VAlUES(v_org,v_id,v_seq_id,v_CREATE_ORG,v_enter_black.name,v_enter_black.cerno,v_law_dict_id,v_enter_black.REVDATE,v_date,v_LOAD_MODE,
v_load_status,v_enter_black.REVDATE,
v_enter_black.REVDATE,V_STATE,null,v_caution_reason,null,null,null,null,V_enter_black.regno,v_ENTER_BLACK.entname,null,null,null,null);
when V_enter_black.ENTTYPE between 6000 and 6399 then
INSERT INTO t_law_caution VAlUES(v_org,v_id,v_seq_id,v_CREATE_ORG,v_enter_black.name,v_enter_black.cerno,v_law_dict_id,v_enter_black.REVDATE,v_date,v_LOAD_MODE,
v_load_status,v_enter_black.REVDATE,
v_enter_black.REVDATE,V_STATE,null,v_caution_reason,null,null,null,null,V_enter_black.regno,v_ENTER_BLACK.entname,null,null,null,null);
else
dbms_output.put_line('其他值不做处理');
end case;
end if;
IF v_operate = 'U' THEN
--判断V_enter_black.S_EXT_VAILDFLAG = 0 时做下面的操作
SELECT id INTO v_id FROM t_id_compare WHERE S_EXT_NODENUM = V_enter_black.S_EXT_NODENUM AND pripid = V_enter_black.Pripid AND table_id = 1 AND table_kind = 1;
delete from t_law_caution where org = v_org and id = v_id and seq_id = v_seq_id;
end if;
end TRG_ENTER_BLACK;执行insert into enter_black(id,REVDATE) values (3,to_date('2007-5-16','yyyy-mm-dd'))报错
ORA-01403: 未找到数据
ORA-06512: 在"GJJ_BAIS.TRG_ENTER_BLACK", line 43
ORA-04088: 触发器 'GJJ_BAIS.TRG_ENTER_BLACK' 执行过程中出错
after INSERT OR UPDATE OR DELETE on enter_black
for each row
declare
-- local variables here
V_enter_black enter_black%ROWTYPE;
v_operate VARCHAR2(1);
v_org NUMBER(12) := 1;
v_id NUMBER(12);
v_seq_id NUMBER(12) := 1;
v_create_org number(12) := 1;
v_law_dict_id number(8) := 105;
v_LOAD_MODE char(1) := 1;
v_load_status char(1) := 'A';
V_STATE char(1) := 'M';
v_date date;
v_caution_reason varchar2(1000);
V_enter_revdate varchar2(20);
v_illegact varchar2(200);
begin IF inserting THEN
---给V_enter_black 付值
v_operate := 'A';
v_enter_black.name :=:new.name;
v_enter_black.cerno :=:new.cerno;
v_enter_black.REVDATE:=:new.REVDATE;
v_ENTER_BLACK.entname:=:new.entname;
V_enter_black.regno :=:new.regno;
V_enter_black.ENTTYPE:=:new.enttype;
v_enter_black.illegact:=:new.illegact;
ELSIF updating THEN
---给V_enter_black 付值
v_operate := 'U';
v_enter_black.name :=:new.name;
v_enter_black.cerno :=:new.cerno;
v_enter_black.REVDATE:=:new.REVDATE;
v_ENTER_BLACK.entname:=:new.entname;
V_enter_black.regno :=:new.regno;
V_enter_black.ENTTYPE:=:new.enttype;
v_enter_black.illegact:=:new.illegact;
END IF;
IF v_operate = 'A' THEN
v_caution_reason :='担任'||v_enter_black.entname||'的法定代表人企业因为'||v_illegact||'在'||V_enter_revdate||'被'||v_ENTER_BLACK.REGORG||'吊销';
select to_char(v_ENTER_BLACK.REVDATE,'yyyy-mm-dd') into V_enter_revdate from ENTER_BLACK;
SELECT s_name into v_illegact from t_dict_ce02 where s_code=v_enter_black.illegact;
SELECT id INTO v_id FROM t_id_compare WHERE S_EXT_NODENUM = V_enter_black.S_EXT_NODENUM AND pripid = V_enter_black.Pripid AND table_id = 1 AND table_kind = 1;
select add_months (v_enter_black.REVDATE,36)-1 into v_date from enter_black;
case
when V_enter_black.ENTTYPE between 1000 and 1099 then
INSERT INTO t_law_caution VAlUES(v_org,v_id,v_seq_id,v_CREATE_ORG,v_enter_black.name,v_enter_black.cerno,v_law_dict_id,v_enter_black.REVDATE,v_date,v_LOAD_MODE,
v_load_status,v_enter_black.REVDATE,
v_enter_black.REVDATE,V_STATE,null,v_caution_reason,null,null,null,null,V_enter_black.regno,v_ENTER_BLACK.entname,null,null,null,null);
when V_enter_black.ENTTYPE between 3000 and 3999 then
INSERT INTO t_law_caution VAlUES(v_org,v_id,v_seq_id,v_CREATE_ORG,v_enter_black.name,v_enter_black.cerno,v_law_dict_id,v_enter_black.REVDATE,v_date,v_LOAD_MODE,
v_load_status,v_enter_black.REVDATE,
v_enter_black.REVDATE,V_STATE,null,v_caution_reason,null,null,null,null,V_enter_black.regno,v_ENTER_BLACK.entname,null,null,null,null);
when V_enter_black.ENTTYPE between 5000 and 5399 then
INSERT INTO t_law_caution VAlUES(v_org,v_id,v_seq_id,v_CREATE_ORG,v_enter_black.name,v_enter_black.cerno,v_law_dict_id,v_enter_black.REVDATE,v_date,v_LOAD_MODE,
v_load_status,v_enter_black.REVDATE,
v_enter_black.REVDATE,V_STATE,null,v_caution_reason,null,null,null,null,V_enter_black.regno,v_ENTER_BLACK.entname,null,null,null,null);
when V_enter_black.ENTTYPE between 6000 and 6399 then
INSERT INTO t_law_caution VAlUES(v_org,v_id,v_seq_id,v_CREATE_ORG,v_enter_black.name,v_enter_black.cerno,v_law_dict_id,v_enter_black.REVDATE,v_date,v_LOAD_MODE,
v_load_status,v_enter_black.REVDATE,
v_enter_black.REVDATE,V_STATE,null,v_caution_reason,null,null,null,null,V_enter_black.regno,v_ENTER_BLACK.entname,null,null,null,null);
else
dbms_output.put_line('其他值不做处理');
end case;
end if;
IF v_operate = 'U' THEN
--判断V_enter_black.S_EXT_VAILDFLAG = 0 时做下面的操作
SELECT id INTO v_id FROM t_id_compare WHERE S_EXT_NODENUM = V_enter_black.S_EXT_NODENUM AND pripid = V_enter_black.Pripid AND table_id = 1 AND table_kind = 1;
delete from t_law_caution where org = v_org and id = v_id and seq_id = v_seq_id;
end if;
end TRG_ENTER_BLACK;执行insert into enter_black(id,REVDATE) values (3,to_date('2007-5-16','yyyy-mm-dd'))报错
ORA-01403: 未找到数据
ORA-06512: 在"GJJ_BAIS.TRG_ENTER_BLACK", line 43
ORA-04088: 触发器 'GJJ_BAIS.TRG_ENTER_BLACK' 执行过程中出错
这句,还有下面的select .. from enter_black
都会出错
行级触发器里不能对触发器基于的表进行读/改操作
为什么这样写?
v_enter_revdate:=to_char(v_ENTER_BLACK.REVDATE,'yyyy-mm-dd');
不行吗?
SELECT s_name into v_illegact from t_dict_ce02 where s_code=NULL;
BEGIN
SELECT s_name into v_illegact from t_dict_ce02 where s_code=v_enter_black.illegact;
EXCEPTION WHEN NO_DATA_FOUND THEN
v_illegact := NULL;
END;