我调成功了,可是表插入记录就出错了。create or replace trigger TRG_I_T_PER_INFO after insert on t_per_info referencing new as a for each row declare -- local variables here ls_sysdate date; begin SELECT sysdate into ls_sysdate FROM dual ; insert into t_hos_register (soci_id,hos_code,name,per_class,hos_level,regi_time, end_time,exam_oper,status,modify_time,note) select a.soci_id,b.hos_code,a.name,a.per_class,b.hos_level,ls_sysdate,ls_sysdate, a.oper_code,'1',ls_sysdate,'' from t_per_info a,t_comp_hos_register b where a.comp_code = b.comp_code and a.soci_id = :a.soci_id; end TRG_I_T_PER_INFO;我是想当这个t_per_info 插入记录后的时候触发这个东西呀,可以写好 了这个触发器后,插入记录就不成功了,为什么呢?
参考: eate or replace trigger CHECKRECORDinsert after insert on CHECKRECORD for each row declare sqlstr varchar(4000); begin sqlstr:=' insert into CHECKRECORD (ADJUSTPLAN,BADNUM,BIANZHI,CHECKBILL,CHECKNUM,FACECOND,GOODNUM,INTROBOOK,OTHERNUM,PASSBOOK,QUALITYBOOK,RECID,SENDNUM,XIUZHU) values ('||getSqlExpression(:new.ADJUSTPLAN,'VARCHAR2') ||','||getSqlExpression(:new.BADNUM,'NUMBER') ||','||getSqlExpression(:new.BIANZHI,'NUMBER') ||','||getSqlExpression(:new.CHECKBILL,'VARCHAR2') ||','||getSqlExpression(:new.CHECKNUM,'NUMBER') ||','||getSqlExpression(:new.FACECOND,'VARCHAR2') ||','||getSqlExpression(:new.GOODNUM,'NUMBER') ||','||getSqlExpression(:new.INTROBOOK,'VARCHAR2') ||','||getSqlExpression(:new.OTHERNUM,'NUMBER') ||','||getSqlExpression(:new.PASSBOOK,'VARCHAR2') ||','||getSqlExpression(:new.QUALITYBOOK,'VARCHAR2') ||','||getSqlExpression(:new.RECID,'VARCHAR2') ||','||getSqlExpression(:new.SENDNUM,'NUMBER') ||','||getSqlExpression(:new.XIUZHU,'NUMBER') ||');'; insert into x_appsendtemp(SENDSQL,SENDFLAG,TABLE_CODE) values(sqlstr,1,'CHECKRECORD'); end CHECKRECORDinsert;
2.ls_soci_id定义了没有赋值.
after insert on t_per_info
referencing new as a
for each row
declare
-- local variables here
ls_sysdate date;
begin
SELECT sysdate into ls_sysdate FROM dual ;
insert into t_hos_register (soci_id,hos_code,name,per_class,hos_level,regi_time,
end_time,exam_oper,status,modify_time,note)
select a.soci_id,b.hos_code,a.name,a.per_class,b.hos_level,ls_sysdate,ls_sysdate,
a.oper_code,'1',ls_sysdate,''
from t_per_info a,t_comp_hos_register b
where a.comp_code = b.comp_code
and a.soci_id = :a.soci_id;
end TRG_I_T_PER_INFO;我是想当这个t_per_info 插入记录后的时候触发这个东西呀,可以写好
了这个触发器后,插入记录就不成功了,为什么呢?
eate or replace trigger CHECKRECORDinsert after
insert on CHECKRECORD for each row
declare sqlstr varchar(4000);
begin
sqlstr:=' insert into CHECKRECORD (ADJUSTPLAN,BADNUM,BIANZHI,CHECKBILL,CHECKNUM,FACECOND,GOODNUM,INTROBOOK,OTHERNUM,PASSBOOK,QUALITYBOOK,RECID,SENDNUM,XIUZHU) values ('||getSqlExpression(:new.ADJUSTPLAN,'VARCHAR2') ||','||getSqlExpression(:new.BADNUM,'NUMBER') ||','||getSqlExpression(:new.BIANZHI,'NUMBER') ||','||getSqlExpression(:new.CHECKBILL,'VARCHAR2') ||','||getSqlExpression(:new.CHECKNUM,'NUMBER') ||','||getSqlExpression(:new.FACECOND,'VARCHAR2') ||','||getSqlExpression(:new.GOODNUM,'NUMBER') ||','||getSqlExpression(:new.INTROBOOK,'VARCHAR2') ||','||getSqlExpression(:new.OTHERNUM,'NUMBER') ||','||getSqlExpression(:new.PASSBOOK,'VARCHAR2') ||','||getSqlExpression(:new.QUALITYBOOK,'VARCHAR2') ||','||getSqlExpression(:new.RECID,'VARCHAR2') ||','||getSqlExpression(:new.SENDNUM,'NUMBER') ||','||getSqlExpression(:new.XIUZHU,'NUMBER') ||');';
insert into x_appsendtemp(SENDSQL,SENDFLAG,TABLE_CODE) values(sqlstr,1,'CHECKRECORD');
end CHECKRECORDinsert;
在触发器中声明使用自治事务
PRAGMA AUTONOMOUS_TRANSACTION;