触发器中的 pragma 自治事务
我在一个表上写了一个触发器，它根据条件从另一个表中删除数据.触发器具有编译指示 Autonomous_transaction，并且触发器按预期工作.但是，我想知道将来是否会出现任何问题，比如数据是否由多个用户/来源同时插入等等......有什么建议吗?
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?
源表t1:
--------------------------------------------
| user_id | auth_name1 | auth_name2 | data |
--------------------------------------------
| 1 | Name1 | Name2 | d1 |
| 2 | Name3 | Name4 | d2 |
| 3 | Name5 | Name1 | d3 |
--------------------------------------------
目标表 t2:
------------------------------------------------
| record_id | identifier | status | data1 |
------------------------------------------------
| 100 | Broken | 11 | Name1 |
| 101 | Reminder | 99 | Name1 |
| 102 | Broken | 99 | Name2 |
| 103 | Broken | 11 | Name4 |
------------------------------------------------
触发代码:
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;
推荐答案
在父事务回滚时，将自治事务用于除日志记录以外的任何事情几乎肯定是错误的.这不是自治事务的一个很好的用途.
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.
例如，如果我更新 t1 中的一行，但我的事务回滚，会发生什么情况.t2 更改已经完成并提交，因此它们不会回滚.这通常意味着 t2 数据现在不正确.事务的全部意义在于确保一组更改是原子的，并且要么完全成功，要么完全恢复.允许代码部分成功几乎从来都不是一个好主意.
What happens, for example, if I update a row in t1 but my transaction rolls back. The t2 changes have already been made and committed so they don't roll back. That generally means that the t2 data 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.
我很难看到在这里使用自主交易会给你带来什么.您经常会看到人们错误地使用自主事务来错误地解决突变触发错误.但是您发布的代码不会产生突变触发器错误，除非 t2 上有一个行级触发器也试图更新 t1 或一些类似的机制引入变异表.但是，如果是这种情况，使用自治事务通常会更糟，因为自治事务无法看到父事务中所做的更改，这几乎肯定会导致代码的行为与您期望的不同.
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 t2 that was also trying to update t1 or 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.
相关文章