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

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

创建一个测试表:

View Code

-- Create tablecreate 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 rowdeclare  -- local variables herebegin  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 rowdeclare  -- local variables herebegin  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 rowdeclare  -- local variables herebegin  :new.c := :new.a + :new.b;end test_trigger;

可以使用:new来访问触发器执行时,当前行的值,这时候再执行性一下插入操作:

可以看到,记录成功插入,并且c字段也变成了我们所预期的值.

这样我们就达到了在触发器中更改基表的目的,不过这个方法也有局限性:

1.触发器的执行时机只能设置before,如果设置after,编译将不能通过.

2.只能对当前被插入或者更新的记录进行操作,无法对其他的记录进行操作.

--------------------------------------------------

问：我要对一张表，在写的SQL语句时更新第二个字段，触发器自动更新第三个字段（需求是时间）？？

答：
create or replace trigger tri_upd
  before update on test_4
  for each row
begin
  :new.cc := 'zhi';
end tri_upd;

经验：在我手动更新第二个字段时，触发器自动更新第三个字段。之前一直以为是用after，觉得触发事件是在我手动更新之后，有了准备的:new.id，触发器才能找到我是在具体修改某行数据，但是一直报错；但后来有人给出的答案是用before，才能正确执行。