有时候我们可能有这种需求,在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.只能对当前被插入或者更新的记录进行操作,无法对其他的记录进行操作.
--------------------------------------------------
问:我要对一张表,在写的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,才能正确执行。