一个触发器的问题，在线等待急......

Question

本帖最后由 yangzong20 于 2013-04-26 14:53:48 编辑

Answer 1

估计是 str变量定义范围过窄，溢出了吧？

Answer 2

to luoyoumou 
关键是我在下面的insert里是直接抓，:NEW.CMNAME,:NEW.CMMOBILE1的呀，没有通过str来赋值呀

Answer 3

提供一下new_sms、YZL_DX_SEND 看看

Answer 4

create table CUSTMEMBER
(
  CMMEMID      VARCHAR2(20) not null,
  CMCUSTID     VARCHAR2(20) not null,
  CMISOWNER    CHAR(1),
  CMRELATION   CHAR(1),
  CMGRADE      CHAR(1),
  CMFLAG1      CHAR(1),
  CMFLAG2      CHAR(1),
  CMFLAG3      CHAR(1),
  CMLMYKJF     NUMBER,
  CMJFXFXE     NUMBER,
  CMLCZHYE     NUMBER,
  CMN1         NUMBER,
  CMN2         NUMBER,
  CMN3         NUMBER,
  CMN4         NUMBER,
  CMN5         NUMBER,
  CMN6         NUMBER,
  CMN7         NUMBER,
  CMN8         NUMBER,
  CMN9         NUMBER,
  CMJFA        NUMBER,
  CMJFB        NUMBER,
  CMJFC        NUMBER,
  CMJFD        NUMBER,
  CMJFE        NUMBER,
  CMJFF        NUMBER,
  CMDECJF      NUMBER,
  CMTOTJF      NUMBER,
  CMXFJE       NUMBER,
  CMMAINTOR    VARCHAR2(15),
  CMMAINTDATE  DATE,
  CMNAME       VARCHAR2(100),
  CMBIRTHDAY   DATE,
  CMBIRTHTYPE  CHAR(1),
  CMSEX        CHAR(1),
  CMADDR       VARCHAR2(200),
  CMADD1       VARCHAR2(100),
  CMADD2       VARCHAR2(100),
  CMADD3       VARCHAR2(100),
  CMADD4       VARCHAR2(100),
  CMZIP        CHAR(6),
  CMIDTYPE     CHAR(1),
  CMIDNO       VARCHAR2(20),
  CMLXTYPE1    CHAR(1),
  CMLXTYPE2    CHAR(1),
  CMLXTYPE3    CHAR(1),
  CMLXTYPE4    CHAR(1),
  CMLXTYPE5    CHAR(1),
  CMTEL        VARCHAR2(30),
  CMMOBILE1    VARCHAR2(30),
  CMMOBILE2    VARCHAR2(30),
  CMFAX        VARCHAR2(30),
  CMEMAIL      VARCHAR2(100),
  CMISEMPLOYEE CHAR(1),
  CMLUNAR      VARCHAR2(8),
  CMLUNARCHN   VARCHAR2(100),
  CMSX         VARCHAR2(2),
  CMCOMPANY    VARCHAR2(100),
  CMDKJF       NUMBER default 0,
  CMDHISJF     NUMBER default 0,
  CMCHR1       VARCHAR2(100),
  CMCHR2       VARCHAR2(100),
  CMCHR3       VARCHAR2(100),
  CMCHR4       VARCHAR2(100),
  CMCHR5       VARCHAR2(100),
  CMCHR6       VARCHAR2(100),
  CMCHR7       VARCHAR2(100),
  CMCHR8       VARCHAR2(100),
  CMCHR9       VARCHAR2(100),
  CMCHR10      VARCHAR2(100),
  CMCHR11      VARCHAR2(100),
  CMCHR12      VARCHAR2(100),
  CMCHR13      VARCHAR2(100),
  CMCHR14      VARCHAR2(100),
  CMCHR15      VARCHAR2(100),
  CMCHR16      VARCHAR2(100),
  CMCHR17      VARCHAR2(100),
  CMCHR18      VARCHAR2(100),
  CMCHR19      VARCHAR2(100),
  CMCHR20      VARCHAR2(100),
  CMCHR21      VARCHAR2(100),
  CMCHR22      VARCHAR2(100),
  CMCHR23      VARCHAR2(100),
  CMCHR24      VARCHAR2(100),
  CMCHR25      VARCHAR2(100),
  CMCHR26      VARCHAR2(100),
  CMCHR27      VARCHAR2(100),
  CMCHR28      VARCHAR2(100),
  CMCHR29      VARCHAR2(100),
  CMCHR30      VARCHAR2(100),
  CMCHR31      VARCHAR2(100),
  CMCHR32      VARCHAR2(100),
  CMCHR33      VARCHAR2(100),
  CMCHR34      VARCHAR2(100),
  CMCHR35      VARCHAR2(100),
  CMCHR36      VARCHAR2(100),
  CMCHR37      VARCHAR2(100),
  CMCHR38      VARCHAR2(100),
  CMCHR39      VARCHAR2(100),
  CMCHR40      VARCHAR2(100),
  CMCHR41      VARCHAR2(100),
  CMCHR42      VARCHAR2(100),
  CMCHR43      VARCHAR2(100),
  CMCHR44      VARCHAR2(100),
  CMCHR45      VARCHAR2(100),
  CMCHR46      VARCHAR2(100),
  CMCHR47      VARCHAR2(100),
  CMCHR48      VARCHAR2(100),
  CMCHR49      VARCHAR2(100),
  CMCHR50      VARCHAR2(100),
  CMNUM1       NUMBER,
  CMNUM2       NUMBER,
  CMNUM3       NUMBER,
  CMNUM4       NUMBER,
  CMNUM5       NUMBER,
  CMNUM6       NUMBER,
  CMNUM7       NUMBER,
  CMNUM8       NUMBER,
  CMNUM9       NUMBER,
  CMNUM10      NUMBER,
  CMNUM11      NUMBER,
  CMNUM12      NUMBER,
  CMNUM13      NUMBER,
  CMNUM14      NUMBER,
  CMNUM15      NUMBER,
  CMNUM16      NUMBER,
  CMNUM17      NUMBER,
  CMNUM18      NUMBER,
  CMNUM19      NUMBER,
  CMNUM20      NUMBER,
  CMMKT        VARCHAR2(20),
  CMKHDATE     DATE,
  CMXFDATE     DATE,
  CMSJDATE     DATE,
  CMJJDATE     DATE
)
tablespace DATA_SPC
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 120
    minextents 1
    maxextents unlimited
  );

Answer 5

上面那个是CUSTMEMBER的表结构下面这个是YZL_DX_SEND的表结构
create table YZL_DX_SEND
(
  CONTENT NVARCHAR2(500),
  MOBILE  NVARCHAR2(30),
  SB      NVARCHAR2(5),
  CDMNO   NVARCHAR2(30),
  CMNAME  NVARCHAR2(30),
  FS_DATE DATE,
  SEND    NVARCHAR2(5),
  ID      NUMBER
)
tablespace DATA_SPC
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64
    minextents 1
    maxextents unlimited
  );

Answer 6

用你的表结构和触发器，测试没有发现问题
测试用例如下：
SQL> insert into CUSTMEMBER(CMMEMID,CMCUSTID,CMNAME,CMMOBILE1)
  2  values('test trigger','CMCUSTID','CMNAME','CMMOBILE1');已创建 1 行。
SQL> select CMNAME,MOBILE from YZL_DX_SEND;CMNAME                                                       MOBILE
------------------------------------------------------------ CMNAME                                                       CMMOBILE1

Answer 7

那真是奇怪我的表里CMNAME,CMMOBILE1里没抓到数据呢？