触发器:在往一个table(A)新增一笔资料后,同时往另外一个table(B)新增一笔资料
编译都过了,就在执行往A table 新增资料时,没有往B table新增资料。我写了个包、包体、触发器。
执行如下语句是报错:
INSERT INTO T00HP017 (T00HP017ID,ORG_CODE,ORG_NAME,PARENTID,STATUS,CREATOR,T00HM001ID,CREATE_DATE,EFFECT_DATE,VERSION_NO,IF_COMPANY,IS_NEW)VALUES ('4006','40','chufaqi','3e0ac83841d64196a9d3e0de042f41d0','1','xll','100556',to_date('2010-10-25','YYYY-MM-DD'),to_date('2010-10-25','YYYY-MM-DD'),1,'1','1')
错误:
SQL Error: ORA-01403: no data found
ORA-06512: at "HR.TR_INS_T00HP017_BEF_T00HZ007", line 5
ORA-04088: error during execution of trigger 'HR.TR_INS_T00HP017_BEF_T00HZ007'
01403. 00000 - "no data found"
*Cause:
*Action:包程序如下:
create or replace package hz007_pkg is
procedure ins_t00hz007(T00HP017ID in T00HP017.T00HP017ID%TYPE,ORG_CODE in T00HP017.ORG_CODE%TYPE,ORG_NAME in T00HP017.ORG_NAME%TYPE,T00HP003ID in T00HP017.T00HP003ID%TYPE, T00HP004ID in T00HP017.T00HP004ID%TYPE,ORG_FULL_PATH in T00HZ007.ORG_FULL_PATH%TYPE) ;
end hz007_pkg;包体程序如下:
create or replace package body hz007_pkg is
procedure ins_t00hz007
(T00HP017ID in T00HP017.T00HP017ID%TYPE,
ORG_CODE in T00HP017.ORG_CODE%TYPE,
ORG_NAME in T00HP017.ORG_NAME%TYPE,
T00HP003ID in T00HP017.T00HP003ID%TYPE,
T00HP004ID in T00HP017.T00HP004ID%TYPE,
FULL_PATH in T00HZ007.ORG_FULL_PATH%TYPE)
is
begin
INSERT INTO T00HZ007 VALUES('4000',T00HP017ID,ORG_CODE,ORG_NAME,T00HP003ID,T00HP004ID,FULL_PATH,'','','','','','','','','','');
end ins_t00hz007;
end hz007_pkg;触发器程序如下:
create or replace trigger tr_ins_t00hp017_bef_t00hz007
after insert on t00hp017
for each row
declare
ORG_FULL_PATH T00HZ007.ORG_FULL_PATH%TYPE;
begin
IF(:new.STATUS='1') THEN
SELECT a.ORG_FULL_PATH into ORG_FULL_PATH FROM T00HZ007 a WHERE a.t00hp017id =:new.PARENTID;
ORG_FULL_PATH :=:OLD.T00HP017ID+ORG_FULL_PATH ;
HZ007_PKG.ins_t00hz007(:old.T00HP017ID ,:old.ORG_CODE,:old.ORG_NAME ,:old.T00HP003ID , :old.T00HP004ID ,ORG_FULL_PATH );
end if;
end tr_ins_t00hp017_bef_t00hz007;
编译都过了,就在执行往A table 新增资料时,没有往B table新增资料。我写了个包、包体、触发器。
执行如下语句是报错:
INSERT INTO T00HP017 (T00HP017ID,ORG_CODE,ORG_NAME,PARENTID,STATUS,CREATOR,T00HM001ID,CREATE_DATE,EFFECT_DATE,VERSION_NO,IF_COMPANY,IS_NEW)VALUES ('4006','40','chufaqi','3e0ac83841d64196a9d3e0de042f41d0','1','xll','100556',to_date('2010-10-25','YYYY-MM-DD'),to_date('2010-10-25','YYYY-MM-DD'),1,'1','1')
错误:
SQL Error: ORA-01403: no data found
ORA-06512: at "HR.TR_INS_T00HP017_BEF_T00HZ007", line 5
ORA-04088: error during execution of trigger 'HR.TR_INS_T00HP017_BEF_T00HZ007'
01403. 00000 - "no data found"
*Cause:
*Action:包程序如下:
create or replace package hz007_pkg is
procedure ins_t00hz007(T00HP017ID in T00HP017.T00HP017ID%TYPE,ORG_CODE in T00HP017.ORG_CODE%TYPE,ORG_NAME in T00HP017.ORG_NAME%TYPE,T00HP003ID in T00HP017.T00HP003ID%TYPE, T00HP004ID in T00HP017.T00HP004ID%TYPE,ORG_FULL_PATH in T00HZ007.ORG_FULL_PATH%TYPE) ;
end hz007_pkg;包体程序如下:
create or replace package body hz007_pkg is
procedure ins_t00hz007
(T00HP017ID in T00HP017.T00HP017ID%TYPE,
ORG_CODE in T00HP017.ORG_CODE%TYPE,
ORG_NAME in T00HP017.ORG_NAME%TYPE,
T00HP003ID in T00HP017.T00HP003ID%TYPE,
T00HP004ID in T00HP017.T00HP004ID%TYPE,
FULL_PATH in T00HZ007.ORG_FULL_PATH%TYPE)
is
begin
INSERT INTO T00HZ007 VALUES('4000',T00HP017ID,ORG_CODE,ORG_NAME,T00HP003ID,T00HP004ID,FULL_PATH,'','','','','','','','','','');
end ins_t00hz007;
end hz007_pkg;触发器程序如下:
create or replace trigger tr_ins_t00hp017_bef_t00hz007
after insert on t00hp017
for each row
declare
ORG_FULL_PATH T00HZ007.ORG_FULL_PATH%TYPE;
begin
IF(:new.STATUS='1') THEN
SELECT a.ORG_FULL_PATH into ORG_FULL_PATH FROM T00HZ007 a WHERE a.t00hp017id =:new.PARENTID;
ORG_FULL_PATH :=:OLD.T00HP017ID+ORG_FULL_PATH ;
HZ007_PKG.ins_t00hz007(:old.T00HP017ID ,:old.ORG_CODE,:old.ORG_NAME ,:old.T00HP003ID , :old.T00HP004ID ,ORG_FULL_PATH );
end if;
end tr_ins_t00hp017_bef_t00hz007;
SELECT a.ORG_FULL_PATH into ORG_FULL_PATH FROM T00HZ007 a WHERE a.t00hp017id =:new.PARENTID;
这里没有符合条件的记录
使用select .. into 时注意,结果集返回多行或不返回数据都将出错。可以使用聚合函数和rownum来控制
楼上的帮我修改下。我没有用过聚合函数和rownum
查询这个的结果 是否唯一
SELECT a.ORG_FULL_PATH FROM T00HZ007 a WHERE a.t00hp017id =1;
SELECT a.ORG_FULL_PATH FROM T00HZ007 a WHERE a.t00hp017id ='3e0ac83841d64196a9d3e0de042f41d0';
--改
SELECT a.ORG_FULL_PATH into ORG_FULL_PATH FROM T00HZ007 a WHERE a.t00hp017id =:new.PARENTID and rownum=1;
--orSELECT max(a.ORG_FULL_PATH) into ORG_FULL_PATH FROM T00HZ007 a WHERE a.t00hp017id =:new.PARENTID;
错误提示:
Error report:
SQL Error: ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "HR.HZ007_PKG" has been invalidated
ORA-04065: not executed, altered or dropped package "HR.HZ007_PKG"
ORA-06508: PL/SQL: could not find program unit being called: "HR.HZ007_PKG"
ORA-06512: at "HR.TR_INS_T00HP017_BEF_T00HZ007", line 17
ORA-01403: no data found
ORA-04088: error during execution of trigger 'HR.TR_INS_T00HP017_BEF_T00HZ007'
04068. 00000 - "existing state of packages%s%s%s has been discarded"
*Cause: One of errors 4060 - 4067 when attempt to execute a stored
procedure.
*Action: Try again after proper re-initialization of any application's
state.create or replace trigger tr_ins_t00hp017_bef_t00hz007
after insert on t00hp017
for each row
declare
ORG_FULL_PATH VARCHAR2(10):='a';
begin
IF(:new.STATUS='1') THEN
SELECT a.ORG_FULL_PATH into ORG_FULL_PATH FROM T00HZ007 a WHERE a.t00hp017id =:new.PARENTID and ROWNUM=1;
ORG_FULL_PATH :=:OLD.T00HP017ID+ORG_FULL_PATH ;
HZ007_PKG.ins_t00hz007(:old.T00HP017ID ,:old.ORG_CODE,:old.ORG_NAME ,:old.T00HP003ID , :old.T00HP004ID ,ORG_FULL_PATH );
end if;
EXCEPTION
when no_data_found then
HZ007_PKG.ins_t00hz007(:old.T00HP017ID ,:old.ORG_CODE,:old.ORG_NAME ,:old.T00HP003ID , :old.T00HP004ID ,:old.T00HP017ID );
end tr_ins_t00hp017_bef_t00hz007;
rownum是伪列 这里rownum=1 就是取一条记录白写了 上面的试都没试 查了没有是不是记录不唯一
多谢了啊,这个确实也是我的一个问题。
菜鸟啊,刚开始学习这个东东。N多不懂。这个例子我已经做好了!触发器改成这样就ok了:
import hr.HZ007_PKG;
create or replace trigger tr_ins_t00hp017_bef_t00hz007
after insert on t00hp017
for each row
declare
ORG_FULL_PATH T00HZ007.ORG_FULL_PATH%TYPE;
begin
IF(:new.STATUS='1') THEN
SELECT a.ORG_FULL_PATH into ORG_FULL_PATH FROM T00HZ007 a WHERE a.t00hp017id =:new.PARENTID and ROWNUM=1;
ORG_FULL_PATH :=:new.T00HP017ID||ORG_FULL_PATH ;
HZ007_PKG.ins_t00hz007(:new.T00HP017ID ,:new.ORG_CODE,:new.ORG_NAME ,:new.T00HP003ID , :new.T00HP004ID ,ORG_FULL_PATH );
end if;
EXCEPTION
when no_data_found then
HZ007_PKG.ins_t00hz007(:new.T00HP017ID ,:new.ORG_CODE,:new.ORG_NAME ,:new.T00HP003ID , :new.T00HP004ID ,:new.T00HP017ID );
end tr_ins_t00hp017_bef_t00hz007;
没往下面看 你的
对于insert 的 触发器 :new.coldelete :OLD.col :new.colupdate :OLD.col :new.col你的下面是的把所有的:old改成:newORG_FULL_PATH :=:OLD.T00HP017ID+ORG_FULL_PATH ;
HZ007_PKG.ins_t00hz007(:old.T00HP017ID ,:old.ORG_CODE,:old.ORG_NAME ,:old.T00HP003ID , :old.T00HP004ID ,ORG_FULL_PATH );
end if;
EXCEPTION
when no_data_found then
HZ007_PKG.ins_t00hz007(:old.T00HP017ID ,:old.ORG_CODE,:old.ORG_NAME ,:old.T00HP003ID , :old.T00HP004ID ,:old.T00HP017ID );