Solved

SQL to show all triggers from a schema

Posted on 2007-11-20
7
1,253 Views
Last Modified: 2013-12-19
Hi,

Is it posible to show all triggers from one schema (oracle 9)?
I want to get this through a winsql statement.

Thanks!
HPvH
0
Comment
Question by:HPvH
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 9

Expert Comment

by:joebednarz
ID: 20320258
If you are logged in as the schema owner:

SELECT * FROM user_triggers;

If you are logged in as a DBA role user:

SELECT * FROM dba_triggers WHERE owner = 'SCOTT';
0
 
LVL 34

Expert Comment

by:johnsone
ID: 20320263
This should give you the list of triggers and the table the trigger is on for an entire schema.

select trigger_name, table_name
from dba_triggers
where owner = '<schema>';
0
 

Author Comment

by:HPvH
ID: 20320305
Hi,

If I run this:
select trigger_name, table_name from dba_triggers where owner = 'SALESMAN';


I get this:
Error: ORA-00942: table or view does not exist
 (State:S0002, Native Code: 3AE)
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20320471
You might not have access to DBA views.

Try this : -

SELECT * FROM user_triggers;
SELECT * FROM all_triggers WHERE owner = 'owner_schema';
0
 
LVL 18

Accepted Solution

by:
Jinesh Kamdar earned 65 total points
ID: 20320474
The last SQL should have read : -

SELECT * FROM all_triggers WHERE owner = 'SALESMAN';
0
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 60 total points
ID: 20320609
Be aware of the following:

USER_TRIGGERS will only show you triggers you own.

ALL_TRIGGERS will only show you triggers you own, unless you have the CREATE ANY TRIGGER privilege.

DBA_TRIGGERS will show all triggers, but you do need privileges to be able to see DBA_TRIGGERS, normally the SELECT ANY DICTIONARY should be sufficient.
0
 

Author Closing Comment

by:HPvH
ID: 31410128
Thanks!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question