oracle Oracle触发器更新同一张表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13492015/
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
Oracle Trigger to update a the same table
提问by wkdshot
I have a trigger which I'm trying to use to update the same table it listens to.
我有一个触发器，我试图用它来更新它监听的同一个表。
However, upon submitting a create or update, I get the following errors:
但是，在提交创建或更新时，我收到以下错误：
ORA-04091: table [username].ADDRESSES is mutating, trigger/function may not see it ORA-06512: at "[username].ADDRESSES_T1", line 9 ORA-04088: error during execution of trigger '[username].ADDRESSES_T1'
I appreciate any help!
我感谢任何帮助！
采纳答案by vkamayiannis
You're updating the same table the trigger is written. You need something like this:
您正在更新触发器写入的同一个表。你需要这样的东西：
create or replace trigger "ADDRESSES_T1"
BEFORE
insert or update on "ADDRESSES"
for each row
begin
DECLARE
l_lat NUMBER;
l_lng NUMBER;
l_postcode VARCHAR2(8) := :NEW.POSTCODE;
BEGIN
brian.POSTCODE_TO_LAT_LNG_GM_API (l_postcode, l_lat, l_lng);
:new.location = SDO_GEOMETRY(2001, --SDO_GTYPE
8307, --SDO_SRID
SDO_POINT_TYPE(l_lng, --X lng
l_lat, --Y lat
null), --Z
null, --SDO_ELEM_INFO_ARRAY
null);
END;
回答by Ajith Sasidharan
Its throwing mutating table error because your trigger is getting fired on table ADDRESSES and inside the trigger body its updating the same table "ADDRESSES".
它抛出变异表错误，因为您的触发器在表 ADDRESSES 上被触发，并且在触发器主体内部更新同一个表“ADDRESSES”。
You can avoid this by using instead of triggers (creating view for table ADDRESSES).
您可以通过使用代替触发器来避免这种情况（为表 ADDRESSES 创建视图）。
here is the sample code
这是示例代码
create or replace view addresses_vw as select * from addresses;
CREATE OR REPLACE TRIGGER ADDRESSES_T1
INSTEAD OF INSERT or UPDATE
ON addresses_vw
FOR EACH ROW
BEGIN
UPDATE addresses
SET <statements> ;
END ADDRESSES_T1;
