CREATE OR REPLACE TRIGGER T_test_UPT after update on test for each rowdeclare
v_id varchar2(30);
v_oldvalues varchar2(3000);
v_newvalues varchar2(3000);
v_value varchar2(1000);
v_sqlflag varchar2(20);
begin
if user='tram_USER' then
return;
end if;
select to_char(sysdate,'yyyymmddhh24misssss')||'_'||to_char(tram_SEQ.nextval,'000000') into v_id from dual;
v_oldvalues := '';
v_newvalues := '';
if :new.CUSCODE is not null then
v_value := ''''||replace(replace(replace(replace(:new.CUSCODE,'`',''''),'''',''''''),chr(13),''),chr(10),chr(11)||chr(9))||'''';
else v_value := 'null';
end if;
if :old.CUSCODE<>:new.CUSCODE or (:old.CUSCODE is not null and :new.CUSCODE is null) or (:old.CUSCODE is null and :new.CUSCODE is not null) then
v_sqlflag := ':q';
else v_sqlflag := '';
end if;
v_newvalues := v_newvalues||'CUSCODE'||v_sqlflag||'='||v_value||chr(13)||chr(10);
if :new.CUSNAME is not null then
v_value := ''''||replace(replace(replace(replace(:new.CUSNAME,'`',''''),'''',''''''),chr(13),''),chr(10),chr(11)||chr(9))||'''';
else v_value := 'null';
end if;
if :old.CUSNAME<>:new.CUSNAME or (:old.CUSNAME is not null and :new.CUSNAME is null) or (:old.CUSNAME is null and :new.CUSNAME is not null) then
v_sqlflag := ':q';
else v_sqlflag := '';
end if;
v_id varchar2(30);
v_oldvalues varchar2(3000);
v_newvalues varchar2(3000);
v_value varchar2(1000);
v_sqlflag varchar2(20);
begin
if user='tram_USER' then
return;
end if;
select to_char(sysdate,'yyyymmddhh24misssss')||'_'||to_char(tram_SEQ.nextval,'000000') into v_id from dual;
v_oldvalues := '';
v_newvalues := '';
if :new.CUSCODE is not null then
v_value := ''''||replace(replace(replace(replace(:new.CUSCODE,'`',''''),'''',''''''),chr(13),''),chr(10),chr(11)||chr(9))||'''';
else v_value := 'null';
end if;
if :old.CUSCODE<>:new.CUSCODE or (:old.CUSCODE is not null and :new.CUSCODE is null) or (:old.CUSCODE is null and :new.CUSCODE is not null) then
v_sqlflag := ':q';
else v_sqlflag := '';
end if;
v_newvalues := v_newvalues||'CUSCODE'||v_sqlflag||'='||v_value||chr(13)||chr(10);
if :new.CUSNAME is not null then
v_value := ''''||replace(replace(replace(replace(:new.CUSNAME,'`',''''),'''',''''''),chr(13),''),chr(10),chr(11)||chr(9))||'''';
else v_value := 'null';
end if;
if :old.CUSNAME<>:new.CUSNAME or (:old.CUSNAME is not null and :new.CUSNAME is null) or (:old.CUSNAME is null and :new.CUSNAME is not null) then
v_sqlflag := ':q';
else v_sqlflag := '';
end if;
CREATE OR REPLACE TRIGGER T_test_UPT after update on test for each rowdeclare
v_id varchar2(30);
v_oldvalues varchar2(3000);
v_newvalues varchar2(3000);
v_value varchar2(1000);
v_sqlflag varchar2(20);
begin
if user='tram_USER' then
return;
end if;
select to_char(sysdate,'yyyymmddhh24misssss')||'_'||to_char(tram_SEQ.nextval,'000000') into v_id from dual;
v_oldvalues := '';
v_newvalues := '';
if :new.CUSCODE is not null then
v_value := ''''||replace(replace(replace(replace(:new.CUSCODE,'`',''''),'''',''''''),chr(13),''),chr(10),chr(11)||chr(9))||'''';
else v_value := 'null';
end if;
if :old.CUSCODE<>:new.CUSCODE or (:old.CUSCODE is not null and :new.CUSCODE is null) or (:old.CUSCODE is null and :new.CUSCODE is not null) then
v_sqlflag := ':q';
else v_sqlflag := '';
end if;
v_newvalues := v_newvalues||'CUSCODE'||v_sqlflag||'='||v_value||chr(13)||chr(10);
if :new.CUSNAME is not null then
v_value := ''''||replace(replace(replace(replace(:new.CUSNAME,'`',''''),'''',''''''),chr(13),''),chr(10),chr(11)||chr(9))||'''';
else v_value := 'null';
end if;
if :old.CUSNAME<>:new.CUSNAME or (:old.CUSNAME is not null and :new.CUSNAME is null) or (:old.CUSNAME is null and :new.CUSNAME is not null) then
v_sqlflag := ':q';
else v_sqlflag := '';
end if;
v_newvalues := v_newvalues||'CUSNAME'||v_sqlflag||'='||v_value||chr(13)||chr(10);
if :new.CUSAREACODE is not null then
v_value := ''''||replace(replace(replace(replace(:new.CUSAREACODE,'`',''''),'''',''''''),chr(13),''),chr(10),chr(11)||chr(9))||'''';
else v_value := 'null';
end if;
if :old.CUSAREACODE<>:new.CUSAREACODE or (:old.CUSAREACODE is not null and :new.CUSAREACODE is null) or (:old.CUSAREACODE is null and :new.CUSAREACODE is not null) then
v_sqlflag := ':q';
else v_sqlflag := '';
end if;
v_newvalues := v_newvalues||'CUSAREACODE'||v_sqlflag||'='||v_value||chr(13)||chr(10);
if :new.CUSLONGNAME is not null then
v_value := ''''||replace(replace(replace(replace(:new.CUSLONGNAME,'`',''''),'''',''''''),chr(13),''),chr(10),chr(11)||chr(9))||'''';
else v_value := 'null';
end if;
if :old.CUSLONGNAME<>:new.CUSLONGNAME or (:old.CUSLONGNAME is not null and :new.CUSLONGNAME is null) or (:old.CUSLONGNAME is null and :new.CUSLONGNAME is not null) then
v_sqlflag := ':q';
else v_sqlflag := '';
end if;
v_newvalues := v_newvalues||'CUSLONGNAME'||v_sqlflag||'='||v_value||chr(13)||chr(10);
if :new.CUSTYPE is not null then
v_value := ''''||replace(replace(replace(replace(:new.CUSTYPE,'`',''''),'''',''''''),chr(13),''),chr(10),chr(11)||chr(9))||'''';
else v_value := 'null';
end if;
if :old.CUSTYPE<>:new.CUSTYPE or (:old.CUSTYPE is not null and :new.CUSTYPE is null) or (:old.CUSTYPE is null and :new.CUSTYPE is not null) then
v_sqlflag := ':q';
else v_sqlflag := '';
end if;
v_newvalues := v_newvalues||'CUSTYPE'||v_sqlflag||'='||v_value||chr(13)||chr(10);
if :new.CUSLINKMAN is not null then
v_value := ''''||replace(replace(replace(replace(:new.CUSLINKMAN,'`',''''),'''',''''''),chr(13),''),chr(10),chr(11)||chr(9))||'''';
else v_value := 'null';
end if;
if :old.CUSLINKMAN<>:new.CUSLINKMAN or (:old.CUSLINKMAN is not null and :new.CUSLINKMAN is null) or (:old.CUSLINKMAN is null and :new.CUSLINKMAN is not null) then
v_sqlflag := ':q';
else v_sqlflag := '';
end if;
v_newvalues := v_newvalues||'CUSLINKMAN'||v_sqlflag||'='||v_value||chr(13)||chr(10);
if instr(v_newvalues,':q=')>0 then
insert into tram_SEND(trIDNO,TABLENAME,OPTTYPE,OLDVALUES,NEWVALUES,DESTFLAG,HANDLEFLAG)
values(v_id,'test','UPT',v_oldvalues,v_newvalues,0,0);
end if;end;
CUSCODE varchar2(10),
CUSNAME varchar2(10),
CUSAREACODE varchar2(10),
CUSLONGNAME varchar2(10),
CUSTYPE varchar2(10),
CUSLINKMAN varchar2(10))
/
create table tram_SEND
(trIDNO varchar2(30),
TABLENAME varchar2(40),
OPTTYPE varchar2(10),
OLDVALUES varchar2(2000),
NEWVALUES varchar2(2000),
DESTFLAG number(8),
HANDLEFLAG number(8))
/
create sequence tram_SEQ
/
然后创建触发器,再执行
SQL> insert into test values(1,2,3,4,5,6);1 row created.SQL> select * from tram_send;no rows selectedSQL> update test set cuscode='3435dfewf';1 row updated.SQL> select count(*) from tram_send; COUNT(*)
----------
1没有出现异常。你是执行怎样的语句出错的,具体的错误信息是什么
DsProvider.Applyupdates(delata,0,maxErrCount);
经调试发现错误:'NOT Find any record to update ,no specified key',在网上查到是触发器
的问题,所以只要禁止触发器处理的影响行数回传给客户端就可以了,但不知具体命令是什么