写了一个after insert的触发器，结果只能插入一条数据，其他的都不能插入

小弟做了基于短信平台的触发器，代码如下：
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 INTO subs FROM zjgsm.ucs_subscription WHERE SERVICE_NUM=:NEW.CONTENT;
          SELECT SERVICE_NUM INTO 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'));
          END IF;
          IF(SER_N IS NOT NULL)THEN
              SELECT BMS_ACCEPT_ID INTO ddnum 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); 
              SELECT DEAL_RESULT INTO IOMstatus FROM zjgsm.if_from_crm_message  WHERE so_order_code=ddnum; 
              IF (IOMstatus IS NOT NULL)THEN            
                  select decode(oo.state,'R','正在执行','M','未执行被撤销','C','已经生成','O','处理结束', 'E','异常结束','S','被挂起','D','在执行中被修改，等待撤销','U','保留')into gdstatus
                  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 ); 
                  select oo.complete_date into 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'));
              END IF;
              IF(IOMstatus IS  NULL)THEN
                  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));         
              END IF;
         END IF;
   END IF;
end TRI_SMSPRO;

说明:发送短信至一个号码，如106559857582，之后短信接口表receive就自动增加一条记录，记录我的号码及相关信息，之后就是我的工作了，我建立触发器进行一系列操作，最后将需要的结果插入表send，短信就自动发送至指定的手机里，我的工作就是做字段的监控，然后处理，得出需要的结果插入send，接口的事情我不需做，现在出现了一个问题，就是没建这个触发器之前发短信至106559857582，任何数据都能插入，可是自从建立了触发器之后，只有一条数据能插入了，如AP575373052（发送AP575373052至106559857582正常，能正常回复手机需要的信息），请大虾们帮我看看是什么问题影响了接口接收表receive，谢谢啦，亟待解决！谢谢！

17 个解决方案

#1

改了下格式和其他，其他数据插不进去，你可以模拟下触发器做的事情，看看每步发生了什么

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; 

#2

你指定是调试还是？

#3

手工测试，drop掉trigger，然后插入一条数据，按照trigger的逻辑做select，看看数据库里发生了什么

#4

drop之后插入数据正常，什么数据都可以插入

#5

drop之后再查询，之前发的信息也都可以查出来，会不会堆积了？

#6

测试步骤：
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吗？

引用 4 楼 zjut_zhanggl 的回复:

drop之后插入数据正常，什么数据都可以插入

#7

我这里指的插入是发短信至106559857582，receive表自动增加记录，而不是我在oracle中insert

#8

现在的问题好像是我建立触发器TRI_SMSPRO影响了receive的查询，我觉得短信接口那边应该是接收到了，只是在我触发器还在的时候，从received中就是查不出来，也是就没有增加记录，那自然不会执行我的触发器。之后在drop触发器TRI_SMSPRO之后可以查询出之前没有查出的结果，说明应该是触发器的建立影响了值得传递（传递到receive表）

#9

sms_receive表不能插入数据了？

#10

去掉for each row,end前加commit试试

#11

是的。短信发至106559857582，sms_receive就不能看到新增记录了，理论上是应该新增的，不知道是什么原因

#12

去掉for each row 就没法用：new了

#13

那我怎么取到新增记录的字段，请指教，哈哈

#14

那倒是，我以为是插入时循环查询导致表被暂时锁。

增加commit呢

#15

我试一把看看

#16

还是不行啊，哈哈

#17

send表的主键是多少？会否主键冲突

建一个临时表吧，在触发器每个insert语句前加一句 
insert into testtable values(1);
insert into testtable values(2);
类似这样检查状态