oracle 觸發器中修改基表解決辦法

有時候我們可能有這種需求,在table上建立一個觸發器,然後當在table中更新A和B欄位時,c欄位變為A+B,這就形成了在觸發器中修改基表資料的情況.

建立一個測試表:

View Code

-- Create table
create table TEST
(
  A  NUMBER,
  B  NUMBER,
  C  NUMBER,
  ID NUMBER not null
)

-- Create/Recreate primary, unique and foreign key constraints 
alter table TEST
add constraint TEST_PRIMARY_ID primary key (ID)

 

看一看下面這個觸發器的寫法:

View Code

create or replace trigger test_trigger
  after insert or update on test  
for each row
declare
-- local variables here
begin
update test set c = :new.a+:new.b where id = :new.id ;
end test_trigger;

我們的目的是在插入或者更新的時候,讓c欄位的值等於欄位a+欄位b的值,我們執行以下sql語句:

View Code

insert into test(id,a,b) values(1,10,20);

 

我們期望會看到c的值自動變成a+b=30,但是我們卻發現如下錯誤:

從錯誤提示來看,是說表已經發生了改變,觸發器不能讀它,因為我們出發器的觸發時機設定的是after,也就是在一條記錄插入完成之後再進行更新操作,但是這時候表已經發生改變,觸發器不能讀這個表了.那我們將觸發器的觸發時間設定before是不是就能達到目的呢,將觸發器修改一下:

View Code

create or replace trigger test_trigger
  before insert or update on test  
for each row
declare
-- local variables here
begin
update test set c = :new.a+:new.b where id = :new.id ;
end test_trigger;

 

編譯一下觸發器,再次執行上面的插入sql語句,這時候我們發現記錄被成功插入了,沒有報錯,這是不是以為著我們就成功了呢,別急,我們先查詢一下test表:

可以看到,記錄雖然成功插入了,但是並沒有達到我們預期的目的,也就是c的值變為30.這是為什麼呢?是我們的觸發器沒有執行嗎?其實這裡觸發器確實執行了,但是因為觸發器執行的時機是before,也就是在插入記錄以前先執行了更新操作,但是這時候表裡面實際上還沒有id=:new.id(這時等於1)的記錄,所以這個更新操作影響的行數為零,接著才執行了插入操作.

那如何才能達到目的呢?其實在觸發器中,我們可以直接存取到當前這條記錄,修改一下觸發器:

View Code

create or replace trigger test_trigger
  before insert or update on test  
for each row
declare
-- local variables here
begin
  :new.c := :new.a + :new.b;
end test_trigger;

可以使用:new來訪問觸發器執行時,當前行的值,這時候再執行性一下插入操作:

可以看到,記錄成功插入,並且c欄位也變成了我們所預期的值.

這樣我們就達到了在觸發器中更改基表的目的,不過這個方法也有局限性:

1.觸發器的執行時機只能設定before,如果設定after,編譯將不能通過.

2.只能對當前被插入或者更新的記錄進行操作,無法對其他的記錄進行操作.