在 Oracle 触发器的同一个表中插入后如何更新？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30977231/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to update after insert in the same table in an Oracle trigger?
提问by newbie
I have a table with columns X, Y and Z. I want to set Z when I insert or update X and Y, so Z=X*Y, using a trigger.
我有一个包含 X、Y 和 Z 列的表。我想在插入或更新 X 和 Y 时设置 Z，因此 Z=X*Y，使用触发器。
create or replace trigger tr_tab
after insert
on tab
for each row
begin
update tab
set z=x*y;
end;
/
But after insert I get an error.
但是插入后出现错误。
回答by Alex Poole
You don't (and can't, generally) update the row you're inserting; you need to change what is being inserted by setting the new Z value for the current row:
您不会（并且通常不能）更新您插入的行；您需要通过为当前行设置新的 Z 值来更改插入的内容：
create or replace trigger tr_tab
before insert or update
on tab
for each row
begin
:new.z := :new.x * :new.y;
end;
/
This refers to the inserted/updated row using the :newpseudorecord syntax.
这是指使用插入/更新的行的:newpseudorecord语法。
This needs to be a before-insert trigger to manipulate the :newvalue for a column; an after-insert for-each-row trigger fires to late to make that change.
这需要是一个插入前触发器来操作:new列的值；为每行插入后触发器触发到很晚才能进行更改。
You could have an after-insert statement level trigger - i.e. what you showed in the question but with the for each rowline removed - but that would update Z for every row in the table even if nothing had changed, doing much more work and generating unnecessary redo. (Read more about the trigger types in the documentation).
您可以有一个插入后语句级触发器 - 即您在问题中显示的内容但for each row删除了该行 - 但即使没有任何更改，它也会为表中的每一行更新 Z，做更多的工作并生成不必要的重做。（在文档中阅读有关触发器类型的更多信息）。
You also have the option of making Z a virtual columnthat is generated automatically from the values of other columns, without needing a trigger.
您还可以选择将 Z设为从其他列的值自动生成的虚拟列，而无需触发器。
回答by hmartos
You have to reference the new inserted values to do that
您必须引用新插入的值才能做到这一点
update tab
set :new.z=:new.x*:new.y;
And if you want to difference between insertions and updates you can do it like this:
如果你想区分插入和更新，你可以这样做：
if (inserting or updating) then
//Do things
end if;
All the information you need to create your trigger is in this page: http://docs.oracle.com/cd/B14117_01/appdev.101/b10795/adfns_tr.htm
创建触发器所需的所有信息都在此页面中：http: //docs.oracle.com/cd/B14117_01/appdev.101/b10795/adfns_tr.htm
