0

How to get a list of triggers with lists of all tables which are used inside the triggers. Means both
1) trigger created of table
2) tables used inside trigger logic ?
Please provide the solution in oracle as well as in sql server?

jitendra joshi
  • 677
  • 5
  • 18
  • Hope link below will help. http://stackoverflow.com/questions/12346914/how-to-find-all-trigger-associated-with-a-table-with-sql-server – japzdivino Oct 05 '15 at 06:36

2 Answers2

2

You'd use ALL_TRIGGERS and ALL_DEPENDENCIES to find all triggers with their depending tables. This is: all tables that the DBMS sees dependant. So where dynamic SQL is used, the DBMS is blind to which tables the dynamic query will contain and you'll miss those.

select 
  t.owner || '.' || t.trigger_name as trigger_name, 
  t.table_owner || '.' || t.table_name as table_name,
  (
    select 
      listagg(d.referenced_owner || '.' || d.referenced_name, ', ') 
        within group (order by d.referenced_owner, d.referenced_name)
    from all_dependencies d 
    where d.owner = t.owner
    and d.name = t.trigger_name
    and d.type = 'TRIGGER'
    and d.referenced_type = 'TABLE'
    and not (d.referenced_owner = t.table_owner and d.referenced_name = t.table_name)
  )  as other_tables
from all_triggers t;

This is for Oracle (I don't know how the same is done in SQL Server. I guess it to be quite similar. SQL Server, too, will have system tables, you could get this information from).

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
1

In Oracle try below query to get desired output

SELECT TRIGGER_NAME,TABLE_NAME,TRIGGER_TYPE, TRIGGERING_EVENT, LINE, TEXT 
FROM USER_TRIGGERS UT, USER_SOURCE US 
WHERE UT.TRIGGER_NAME=US.NAME;
Roman Marusyk
  • 23,328
  • 24
  • 73
  • 116
Bala S
  • 495
  • 1
  • 6
  • 17