oracle 触发器中的 pragma 自治事务
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23774882/
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
pragma autonomous_transaction in a trigger
提问by Jaanna
I have written a trigger on one table which deletes data from other table upon a condition. The trigger has pragma autonomous_transaction, and trigger works as intended. However, I do wonder if there can be any problems in future, say if data is inserted by multiple users/sources at the same time etc...Any suggestions?
我在一个表上写了一个触发器，它根据条件从另一个表中删除数据。触发器具有 pragma 自治事务，并且触发器按预期工作。但是，我确实想知道将来是否会出现任何问题，比如数据是否由多个用户/来源同时插入等等......有什么建议吗？
Source table t1:
源表 t1：
--------------------------------------------
| user_id | auth_name1 | auth_name2 | data |
--------------------------------------------
| 1 | Name1 | Name2 | d1 |
| 2 | Name3 | Name4 | d2 |
| 3 | Name5 | Name1 | d3 |
--------------------------------------------
Target table t2:
目标表 t2：
------------------------------------------------
| record_id | identifier | status | data1 |
------------------------------------------------
| 100 | Broken | 11 | Name1 |
| 101 | Reminder | 99 | Name1 |
| 102 | Broken | 99 | Name2 |
| 103 | Broken | 11 | Name4 |
------------------------------------------------
Trigger code:
触发代码：
create or replace trigger "ca"."t$t1"
after update of auth_name1, auth_name2 on ca.t1
for each row
declare
pragma autonomous_transaction;
begin
if :new.auth_name1 is not null and :new.auth_name2 is not null then
delete from ca.t2 ml
where ml.identifier = 'Broken'
and data1 = regexp_substr(:new.auth_name1, '\S+$')||' '||regexp_substr(:new.auth_name1, '^\S+')
and status = 11;
commit;
end if;
end t$t1;
回答by Justin Cave
Using an autonomous transaction for anything other than logging that you want to be preserved when the parent transaction rolls back is almost certainly an error. This is not a good use of an autonomous transaction.
当父事务回滚时，将自治事务用于除日志记录之外的任何事情几乎肯定是错误的。这不是自治事务的一个很好的用途。
What happens, for example, if I update a row in t1but my transaction rolls back. The t2changes have already been made and committed so they don't roll back. That generally means that the t2data is now incorrect. The whole point of transactions is to ensure that a set of changes is atomic and is either completely successful or completely reverted. Allowing code to be partially successful is almost never a good idea.
例如，如果我更新了一行t1但我的事务回滚了会发生什么。该t2变化已经做出，并承诺使他们不回滚。这通常意味着t2数据现在不正确。事务的全部意义在于确保一组更改是原子的，并且要么完全成功，要么完全恢复。允许代码部分成功几乎从来都不是一个好主意。
I'm hard-pressed to see what using an autonomous transaction buys you here. You'll often see people incorrectly using autonomous transactions to incorrectly work around mutating trigger errors. But the code you posted wouldn't generate a mutating trigger error unless there was a row-level trigger on t2that was also trying to update t1or some similar mechanism that was introducing a mutating table. If that's the case, though, using an autonomous transaction is generally even worse because the autonomous transaction then cannot see the changes being made in the parent transaction which almost certainly causes the code to behave differently than you would desire.
我很难在这里看到使用自主交易会给你带来什么。您经常会看到人们错误地使用自主事务来错误地解决突变触发错误。但是您发布的代码不会产生变异触发器错误，除非有一个行级触发器t2也在尝试更新t1或引入变异表的一些类似机制。但是，如果是这种情况，使用自治事务通常会更糟，因为自治事务无法看到父事务中所做的更改，这几乎肯定会导致代码的行为与您期望的不同。
