CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsql
animark
animark

Reputation: 101

How can we figure out that a column in my oracle table is being populated/updated by a trigger of another table?

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

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".

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.

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.

Regards a.b

Upvotes: 2

Views: 2894

Answers (3)

Jon Heller
Jon Heller

Reputation: 36862

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.

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.

Example:

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

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.

Upvotes: 2

krokodilko
krokodilko

Reputation: 36107

Simple example:

create table table_a(
 id number primary key,
 val varchar2( 100 )
);

create table table_b(
 len number
);

insert into table_b values ( 0 );

set define off
create or replace trigger after_table_a
after insert on table_a for each row
begin
   UpDate 
     table_B
       set len = len + length( :new.val );
end;
/

insert into table_a values ( 1, 'Ala ma kota');
insert into table_a values ( 2, 'As to ali pies');
commit;

select * from table_b;

       LEN
----------
        25



And the query:

select trigger_name, 
       regexp_substr( trigger_body, 'update\s+table_b',1,1,'i') update_command
from (
  select ut.trigger_name, 
         dbms_metadata.GET_DDL('TRIGGER', ut.trigger_name) trigger_body 
  from user_dependencies ud
  join user_triggers ut on ( ud.type = 'TRIGGER' 
                             and ut.trigger_name = ud.name 
                             and ut.table_name <> ud.referenced_name )
  where ud.referenced_name = 'TABLE_B'
)
where regexp_instr( trigger_body, 'update\s+table_b',1,1,0,'i') > 0 ;


TRIGGER_NAME  UPDATE_COMMAND
------------- ------------------ 
AFTER_TABLE_A UpDate
                table_B

Upvotes: 0

Sebas
Sebas

Reputation: 21532

SELECT *
FROM 
    user_sources
WHERE
    type = 'TRIGGER'
AND UPPER(text) LIKE '%UPDATE A%';

But it won't work if the query is in two lines such as :

UPDATE
    A
SET
   ...

because text matches to a given line in the corresponding object.

Upvotes: 1

Related Questions