今天在寫一個觸發器的時候遇到一個問題,因為要用到新插入表中的資料進行查詢,是以就遇到了一個問題:
解決辦法是在declare中加上一句話:pragma autonomous_transaction
但是這樣又會遇到一個問題:
解決辦法是在dml語句後面加上commit
附上語句如下:
create or replace trigger "tr_cms_memberinfo"
after insert or update on membername
for each row
declare
pragma autonomous_transaction;
rowcount integer;
cltparentno varchar2(30);
begin
select count(*) into rowcount from membername where cltno = :new.cltno;
if rowcount > 0 then
begin
begin
select cltno into cltparentno from membername where id = :new.parent_id;
exception when no_data_found then
cltparentno := null;
end;
update zknmember set parentno = cltparentno,lastupdatetime = sysdate where cltno = :new.cltno;
commit;
end;
else
insert into zknmember (cltno,parentno,feemethod,feedir1,feedir2,createtime,lastupdatetime) values (:new.cltno,cltparentno,1,1,1,sysdate,sysdate);
end if;
end;