改了下格式和其他,其他数据插不进去,你可以模拟下触发器做的事情,看看每步发生了什么create or replace trigger TRI_SMSPRO after insert on smssend.sms_receive for each row declare -- local variables here subs number(20); SER_N VARCHAR2(30); ddnum varchar2(30); IOMstatus char(1); gdstatus varchar2(140); pl_time date; begin IF(:NEW.SMS_NUMBER='2')THEN SELECT SUBSCRIPTION_ID,SERVICE_NUM INTO subs,SER_N FROM zjgsm.ucs_subscription WHERE SERVICE_NUM = :NEW.CONTENT; IF SER_N IS NULL THEN insert into smssend.sms_send(id,sms_number, mobile, content, plan_time) values(sms_seq.nextval,'2',:new.mobile,'号码有误,请重新核对号码!',TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss')); ELSE SELECT DEAL_RESULT INTO IOMstatus FROM zjgsm.if_from_crm_message WHERE so_order_code in (SELECT BMS_ACCEPT_ID FROM zjucrm1o.BMS_ACCEPT_200908@crmdb WHERE RESERVED2 = subs AND CONFIRM_TIME = (SELECT MAX(CONFIRM_TIME) FROM zjucrm1o.BMS_ACCEPT_200908@crmdb WHERE RESERVED2=subs) ); IF IOMstatus IS NOT NULL THEN select decode(oo.state,'R','正在执行','M','未执行被撤销','C','已经生成','O','处理结束', 'E','异常结束','S','被挂起','D','在执行中被修改,等待撤销','U','保留') , oo.complete_date into gdstatus , pl_time from zjuiom1o.oms_order_instance@CRMDB RR , zjuiom1o.oms_task_instance@crmdb OO where RR.so_order_code = ddnum AND rr.order_id = oo.order_Id and oo.complete_date = (select max(oo.complete_date) from zjuiom1o.oms_order_instance@CRMDB RR , zjuiom1o.oms_task_instance@crmdb OO where RR.so_order_code=ddnum and rr.order_id=oo.order_Id ); insert into smssend.sms_send(id, sms_number, mobile, content, plan_time) values(sms_seq.nextval,'2',:new.mobile,gdstatus,to_char(pl_time,'yyyy-mm-dd hh24:mi:ss')); ELSE insert into smssend.sms_send(id,sms_number, mobile, content, plan_time) values(sms_seq.nextval,'2',:new.mobile,'未从CRM传值到IOM!',to_char(pl_time,'yyyy-mm-dd hh24:mi:ss')); END IF; END IF; END IF; end TRI_SMSPRO;
after insert on smssend.sms_receive
for each row
declare
-- local variables here
subs number(20);
SER_N VARCHAR2(30);
ddnum varchar2(30);
IOMstatus char(1);
gdstatus varchar2(140);
pl_time date;
begin
IF(:NEW.SMS_NUMBER='2')THEN
SELECT SUBSCRIPTION_ID,SERVICE_NUM INTO subs,SER_N
FROM zjgsm.ucs_subscription
WHERE SERVICE_NUM = :NEW.CONTENT; IF SER_N IS NULL THEN
insert into smssend.sms_send(id,sms_number, mobile, content, plan_time)
values(sms_seq.nextval,'2',:new.mobile,'号码有误,请重新核对号码!',TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss'));
ELSE
SELECT DEAL_RESULT INTO IOMstatus
FROM zjgsm.if_from_crm_message
WHERE so_order_code in (SELECT BMS_ACCEPT_ID FROM zjucrm1o.BMS_ACCEPT_200908@crmdb
WHERE RESERVED2 = subs
AND CONFIRM_TIME = (SELECT MAX(CONFIRM_TIME)
FROM zjucrm1o.BMS_ACCEPT_200908@crmdb
WHERE RESERVED2=subs)
); IF IOMstatus IS NOT NULL THEN
select decode(oo.state,'R','正在执行','M','未执行被撤销','C','已经生成','O','处理结束', 'E','异常结束','S','被挂起','D','在执行中被修改,等待撤销','U','保留')
, oo.complete_date
into gdstatus
, pl_time
from zjuiom1o.oms_order_instance@CRMDB RR
, zjuiom1o.oms_task_instance@crmdb OO
where RR.so_order_code = ddnum
AND rr.order_id = oo.order_Id
and oo.complete_date = (select max(oo.complete_date)
from zjuiom1o.oms_order_instance@CRMDB RR
, zjuiom1o.oms_task_instance@crmdb OO
where RR.so_order_code=ddnum
and rr.order_id=oo.order_Id ); insert into smssend.sms_send(id, sms_number, mobile, content, plan_time)
values(sms_seq.nextval,'2',:new.mobile,gdstatus,to_char(pl_time,'yyyy-mm-dd hh24:mi:ss')); ELSE
insert into smssend.sms_send(id,sms_number, mobile, content, plan_time)
values(sms_seq.nextval,'2',:new.mobile,'未从CRM传值到IOM!',to_char(pl_time,'yyyy-mm-dd hh24:mi:ss'));
END IF;
END IF;
END IF;
end TRI_SMSPRO;
1.准备一条数据,SMS_NUMBER='2',CONTENT='XXX'然后
SELECT SUBSCRIPTION_ID,SERVICE_NUM INTO subs,SER_N
FROM zjgsm.ucs_subscription
WHERE SERVICE_NUM = 'XXX';如果SER_N是空,
insert into smssend.sms_send(id,sms_number, mobile, content, plan_time)
values(sms_seq.nextval,'2',:new.mobile,'号码有误,请重新核对号码!',TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss')); 然后测试其他情况,看是否正常。
几种情况测完后 create trigger
再按照刚才的插入数据插入试试,还有sms_send主键是id吗?
insert into testtable values(1);
insert into testtable values(2);
类似这样检查状态