Oracle: SQL Query to Find All the Triggers Belonging to the Tables

Oracle: SQL query to find all the triggers belonging to the tables?

The following will work independent of your database privileges:

select * from all_triggers
where table_name = 'YOUR_TABLE'

The following alternate options may or may not work depending on your assigned database privileges:

select * from DBA_TRIGGERS

or

select * from USER_TRIGGERS

How can I get all the triggers linked to a specific table?

SELECT * FROM USER_TRIGGERS WHERE TABLE_NAME = 'NAME_OF_YOUR_TABLE';

How to find all trigger associated with a table with SQL Server?

You can do this simply with SSMS. Just go to your table name and expand the Triggers node to view a list of triggers associated with that table. Right click to modify your trigger.

Get the names of all Triggers currently in the database via SQL statement (Oracle SQL Developer)

What you have is pretty close:

select owner, object_name
from all_objects
where object_type = 'TRIGGER'

Or more usefully:

select owner, trigger_name, table_owner, table_name, triggering_event
from all_triggers

all_triggers has other columns to give you more information that all_objects does, like when the trigger fires. You can get more information about this and other useful data dictionary view in the documentation.

Find all other tables that use a current table (oracle database)

You can use the data dictionaries to do this, but which data dictionary to use depends on the type of object in question. Because you want to find several different types of objects, you'll need to use several dictionaries, and, if you want the result all in one query, you will have to connect a few queries together via unions.

The following query (2 queries connected into one by a union) will show you tables with keys referencing table xyz in schema xyz, and also the names of triggers using table xyz in schema xyz (replace table and schema name with whatever you're searching on:

select 'Table has key referencing this table' as match_type, table_name
  from all_constraints
 where constraint_type = 'R'
   and r_constraint_name in
       (select constraint_name
          from all_constraints
         where constraint_type in ('P', 'U')
           and table_name = 'TABLE_XYZ'
           and owner = 'SCHEMA_XYZ')
union all
select distinct 'Table is used by this trigger', trigger_name
  from all_triggers
 where table_name = 'TABLE_XYZ'
   and owner = 'SCHEMA_XYZ'

For other types of objects, use the same method with the next appropriate dictionary, and connect by a union. You will find this helpful:

http://www.oracle.com/pls/tahiti/tahiti.catalog_views

Note that I use the all_ dictionaries in the query above. Each has an equivalent dba_ and user_ dictionary. The all_ ones act as a dictionary for all objects to which the currently logged in user (you) has access. The dba_ one acts as a dictionary for all objects in the database, but you need to have the privilege to use the dba_ dictionaries, so I didn't use them in the above query (if you do have access, replace all_ with dba_). The user_ dictionaries act as a dictionary for all objects owned by the current user/schema.

I need to check Table structure with triggers, constraints

The table definition with constraints:

select DBMS_METADATA.GET_DDL('TABLE', 'MY_TABLE' )
 from dual;

The triggers on table:

select DBMS_METADATA.GET_DDL('TRIGGER',trigger_name) from user_triggers where table_name='MY_TABLE';

Get list of all tables in Oracle?

SELECT owner, table_name
  FROM dba_tables

This is assuming that you have access to the DBA_TABLES data dictionary view. If you do not have those privileges but need them, you can request that the DBA explicitly grants you privileges on that table, or, that the DBA grants you the SELECT ANY DICTIONARY privilege or the SELECT_CATALOG_ROLE role (either of which would allow you to query any data dictionary table). Of course, you may want to exclude certain schemas like SYS and SYSTEM which have large numbers of Oracle tables that you probably don't care about.

Alternatively, if you do not have access to DBA_TABLES, you can see all the tables that your account has access to through the ALL_TABLES view:

SELECT owner, table_name
  FROM all_tables

Although, that may be a subset of the tables available in the database (ALL_TABLES shows you the information for all the tables that your user has been granted access to).

If you are only concerned with the tables that you own, not those that you have access to, you could use USER_TABLES:

SELECT table_name
  FROM user_tables

Since USER_TABLES only has information about the tables that you own, it does not have an OWNER column – the owner, by definition, is you.

Oracle also has a number of legacy data dictionary views-- TAB, DICT, TABS, and CAT for example-- that could be used. In general, I would not suggest using these legacy views unless you absolutely need to backport your scripts to Oracle 6. Oracle has not changed these views in a long time so they often have problems with newer types of objects. For example, the TAB and CAT views both show information about tables that are in the user's recycle bin while the [DBA|ALL|USER]_TABLES views all filter those out. CAT also shows information about materialized view logs with a TABLE_TYPE of "TABLE" which is unlikely to be what you really want. DICT combines tables and synonyms and doesn't tell you who owns the object.

---

Related Topics