Solved

SQL to show all triggers from a schema

Posted on 2007-11-20
7
1,243 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

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.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Oracle 12c 10 100
oracle 10G 5 48
statspack purge automate 7 27
Getting a return value from an IN OUT parameter in Oracle? 7 29
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
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…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
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.

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now