有一表,结构为:id name je1 je2 je,想做一触发器,在插入或更新数据时只插入或更新je1和je2,然后通过触发器将je1+je2的和插入或更新到je字段中,现将我写的考下:
create or replace trigger a_tig
after insert or update on aaa for each rowdeclare
tid number;
begin
tid:=:old.id;
update aaa set je =:new.je1+je2 where id=tid;
end;
编译通过,但调用时出错,说是表发生变化,但触发器或函数不能读它,错误代码ORA-04091,就在更新语句行出错。好久没用过了,不知咋回事,求教……
create or replace trigger a_tig
after insert or update on aaa for each rowdeclare
tid number;
begin
tid:=:old.id;
update aaa set je =:new.je1+je2 where id=tid;
end;
编译通过,但调用时出错,说是表发生变化,但触发器或函数不能读它,错误代码ORA-04091,就在更新语句行出错。好久没用过了,不知咋回事,求教……
--对本表的自操作要加上自治的事务!create or replace trigger a_tig
after insert or update on aaa for each row
pragma autonomous_transaction;
declare
tid number;
begin
tid:=:old.id;
update aaa set je =:new.je1+je2 where id=tid;
end;
before insert or update on aaa for each rowdeclare
begin
new.je := new.je1 + new.je2;
end;
CREATE OR REPLACE TRIGGER a_tig
BEFORE INSERT OR UPDATE OF je ON aaa
FOR EACH ROW
BEGIN
if ( :new.je= 0 or :new.jeis null ) then
SELECT new.je1 + new.je2 into :new.je from dual;
end if;
END;