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

[yangzong20]

我写了一个insert触发器，目的是想在某个表新增记录的时候抓取新增的这条记录莫些字段添加到其他表里去，我运行后发现新增记录的有些字段抓出来是空的，麻烦各位大哥给看看。小弟万分感谢。

CREATE OR REPLACE TRIGGER new_sms
before insert
on CUSTMEMBER FOR EACH ROW
declare
str varchar2(150);
begin
str:='尊敬的'||:NEW.CMNAME||'，欢迎您加入VIP会员俱乐部，我们将竭诚为您提供优质的服务，欢迎您拨打客服专线';
--if (length(TRIM(:new.CMMOBILE1)) = 11) then
INSERT INTO YZL_DX_SEND (CDMNO,CMNAME,CONTENT,MOBILE,SB,FS_DATE,send) values(:NEW.CMCUSTID,:NEW.CMNAME,str,:NEW.CMMOBILE1,'3',sysdate,'N');
--END IF;
end;

编译是没出错，但添加记录后发现只有:NEW.CMCUSTID这个字段有值，:NEW.CMNAME,:NEW.CMMOBILE1这两个字段都没值是空的，我添加 CUSTMEMBER 表记录的时候这两个字段是有值的，麻烦各位大哥帮忙看看，小弟万分感谢。

[luoyoumou]

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

[yangzong20]

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

[sych888]

提供一下new_sms、YZL_DX_SEND 看看

[sych888]

引用 3 楼 sych888 的回复:

提供一下new_sms、YZL_DX_SEND 看看

表结构

[yangzong20]

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 );

[yangzong20]

上面那个是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 );

[sych888]

用你的表结构和触发器，测试没有发现问题 测试用例如下： 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

[yangzong20]

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