oracle 触发器中修改基表解决办法

有时候我们可能有这种需求,在table上建立一个触发器,然后当在table中更新A和B字段时,c字段变为A+B,这就形成了在触发器中修改基表数据的情况.

创建一个测试表:

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

 

看一看下面这个触发器的写法:

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语句:

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

 

我们期望会看到c的值自动变成a+b=30,但是我们却发现如下错误:

从错误提示来看,是说表已经发生了改变,触发器不能读它,因为我们出发器的触发时机设置的是after,也就是在一条记录插入完成之后再进行更新操作,但是这时候表已经发生改变,触发器不能读这个表了.那我们将触发器的触发时间设置before是不是就能达到目的呢,将触发器修改一下:

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)的记录,所以这个更新操作影响的行数为零,接着才执行了插入操作.

那如何才能达到目的呢?其实在触发器中,我们可以直接访问到当前这条记录,修改一下触发器:

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.只能对当前被插入或者更新的记录进行操作,无法对其他的记录进行操作.