Oracle PL/SQL：从触发器调用过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20840060/
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 PL/SQL: Calling a procedure from a trigger
提问by Wabbit
I get this error when ever I try to fire a trigger after insert on passengers table. this trigger is supposed to call a procedure that takes two parameters of the newly inserted values and based on that it updates another table which is the booking table. however, i am getting this error:
在插入乘客表后尝试触发触发器时，我会收到此错误。此触发器应该调用一个过程，该过程采用新插入值的两个参数，并基于此更新另一个表，即预订表。但是，我收到此错误：
ORA-04091: table AIRLINESYSTEM.PASSENGER is mutating, trigger/function may not see it
ORA-06512: at "AIRLINESYSTEM.CALCULATE_FLIGHT_PRICE", line 11 ORA-06512: at
"AIRLINESYSTEM.CALCULATE_FLIGHT_PRICE", line 15 ORA-06512: at
"AIRLINESYSTEM.CALCULATE_FLIGHT_PRICE_T1", line 3 ORA-04088: error during execution of
trigger 'AIRLINESYSTEM.CALCULATE_FLIGHT_PRICE_T1' (Row 3)
I complied and tested the procedure in the SQL command line and it works fine. The problem seems to be with the trigger. This is the trigger code:
我在 SQL 命令行中编译并测试了该过程，它工作正常。问题似乎出在触发器上。这是触发代码：
create or replace trigger "CALCULATE_FLIGHT_PRICE_T1"
AFTER
insert on "PASSENGER"
for each row
begin
CALCULATE_FLIGHT_PRICE(:NEW.BOOKING_ID);
end;?????
Why is the trigger isn't calling the procedure?
为什么触发器不调用过程？
回答by Rob van Wijk
You are using database triggers in a way they are not supposed to be used. The database trigger tries to read the table it is currently modifying. If Oracle would allow you to do so, you'd be performing dirty reads. Fortunately, Oracle warns you for your behaviour, and you can modify your design.
您正在以不应该使用的方式使用数据库触发器。数据库触发器尝试读取它当前正在修改的表。如果 Oracle 允许您这样做，您将执行脏读。幸运的是，Oracle 会警告您的行为，您可以修改您的设计。
The best solution would be to create an API. A procedure, preferably in a package, that allows you to insert passengers in exactly the way you would like it. In pseudo-PL/SQL-code:
最好的解决方案是创建一个 API。一个程序，最好是在一个包裹中，允许您以您想要的方式插入乘客。在伪 PL/SQL 代码中：
procedure insert_passenger
( p_passenger_nr in number
, p_passenger_name in varchar2
, ...
, p_booking_id in number
, p_dob in number
)
is
begin
insert into passenger (...)
values
( p_passenger_nr
, p_passenger_name
, ...
, p_booking_id
, p_dob
);
calculate_flight_price
( p_booking_id
, p_dob
);
end insert_passenger;
/
Instead of your insert statement, you would now call this procedure. And your mutating table problem will disappear.
您现在将调用此过程，而不是插入语句。你的变异表问题将消失。
If you insist on using a database trigger, then you would need to avoid the select statement in cursor c_passengers. This doesn't make any sense: you have just inserted a row into table passengers and know all the column values. Then you call calculate_flight_price to retrieve the column DOB, which you already know. Just add a parameter P_DOB to your calculate_flight_price procedure and call it with :new.dob, like this:
如果您坚持使用数据库触发器，则需要避免游标 c_passengers 中的 select 语句。这没有任何意义：您刚刚在表乘客中插入了一行并知道所有列值。然后您调用calculate_flight_price 来检索您已经知道的列DOB。只需在您的 calculate_flight_price 过程中添加一个参数 P_DOB 并使用 :new.dob 调用它，如下所示：
create or replace trigger calculate_flight_price_t1
after insert on passenger
for each row
begin
calculate_flight_price
( :new.booking_id
, :new.dob
);
end;
回答by SriniV
Oh my goodness... You are trying a Dirty Readin the cursor. This is a bad design.
If you allow a dirty read, it return the wrong answer, but also it returns an answer that never existed in the table. In a multiuser database, a dirty read can be a dangerous feature.
哦，我的天……您正在Dirty Read光标中尝试 a 。这是一个糟糕的设计。如果允许脏读，它会返回错误的答案，但也会返回表中从未存在的答案。在多用户数据库中，脏读可能是一个危险的特性。
The point here is that dirty read is not a feature; rather, it's a liability. In Oracle Database, it's just not needed. You get all of the advantages of a dirty read—no blocking—without any of the incorrect results.
这里的重点是脏读不是一个特性；相反，这是一种责任。在 Oracle 数据库中，它只是不需要。您可以获得脏读的所有优点——无阻塞——而不会产生任何不正确的结果。
Read more on "READ UNCOMMITTED isolation level"which allows dirty reads. It provides a standards-based definition that allows for nonblocking reads.
阅读更多关于"READ UNCOMMITTED isolation level"允许脏读的信息。它提供了一个基于标准的定义，允许非阻塞读取。
Other way round
另一边
You are misusing the trigger. I mean wrong trigger used.
你在滥用触发器。我的意思是使用了错误的触发器。
you insert / update a row in table A and a trigger on table A (for each row) executes a query on table A (through a procedure)??!!!
您在表 A 中插入/更新一行，并且表 A 上的触发器（对于每一行）在表 A 上执行查询（通过过程）？？！！！
Oracle throws an ORA-04091 which is an expected and normal behavior, Oracle wants to protect you from yourself since it guarantees that each statement is atomic (i.e will either fail or succeed completely) and also that each statement sees a consistent view of the data
Oracle 抛出 ORA-04091，这是一种预期和正常的行为，Oracle 希望保护您免受自己的侵害，因为它保证每个语句都是原子的（即要么失败要么完全成功），并且每个语句都看到一致的数据视图
You would expect the query (2) not to see the row inserted on (1). This would be in contradiction
您希望查询 (2) 不会看到插入到 (1) 上的行。这会自相矛盾
Solution: -- use beforeinstead of after
解决方案：--使用before代替after
CREATE OR REPLACE TRIGGER SOMENAME
BEFORE INSERT OR UPDATE ON SOMETABLE
