问题描述

考虑一个场景，其中有两个表A"和B".

Consider a scenario, where there are two tables "A" and "B".

表A"有一个触发器Ta"[在我加入这个项目之前很久就写好了，因此我完全不知道触发器]，它更新了表B"中名为colB"的列.

Table "A" has a trigger "Ta" [written long before me joining this project and thus I'm completely unaware of the trigger], which updates a column named "colB" in table "B".

现在，由于我主要使用表B"并担心colB"的获取方式，因此我不知道触发器Ta"是否正在更新此列.

Now, since I'm mostly using table "B" and concerned about the way "colB" is getting, I won't know if trigger "Ta" is updating this column.

所以我的问题是，是否有直接的 oracle 查询/方法来查找一个表中的列是否被另一个表上运行的任何触发器更新?

So my question is, is there a direct oracle query/way to find if a column in one table is getting updated by any trigger running on another table?

预先感谢您在这方面教育我.

Thanks in advance for educating me on this.

问候a.b

推荐答案

Oracle 细粒度依赖关系跟踪知道使用了哪些列.不幸的是，无法跟踪该依赖项是用于读取还是写入.并且没有默认的 DBA_DEPENDENCY_COLUMNS 视图来查找此信息.

Oracle fine grained dependency tracking knows which columns are used. Unfortunately, there is no way to track if that dependency is for reading or writing. And there is no default DBA_DEPENDENCY_COLUMNS view to find this information.

但幸运的是 Rob van Wijk 创建了这样的视图.他的博客有更多信息，包括授权和 create view 声明，大约在页面的一半.

But luckily Rob van Wijk has created such a view. His blog has some more information, including the grants and create view statement, about half-way down the page.

示例:

drop table a;
drop table b;

create table a(colA number);
create table b(colB number, read_only number, not_used number);

create or replace trigger Ta
after update or insert or delete on a
begin
    update b set colB = read_only;
end;
/

--What triggers are referencing B's columns?
select owner, name, type, referenced_column
from dba_dependency_columns
where referenced_owner = user
    and referenced_name = 'B'
    and type = 'TRIGGER';

OWNER    NAME  TYPE     REFERENCED_COLUMN
-----    ----  ----     -----------------
JHELLER  TA    TRIGGER  COLB
JHELLER  TA    TRIGGER  READ_ONLY

该视图使用了几个未记录的表和一些高级 SQL 功能.这种视图在生产服务器上不是一个好主意.但它可能比任何涉及解析 SQL 的解决方案准确得多.

The view uses several undocumented tables and some advanced SQL features. This view would not be a good idea on a production server. But it is probably much more accurate than any solution that involves parsing SQL.

这篇关于我们如何确定我的 oracle 表中的列正在被另一个表的触发器填充/更新?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！