Solved

SQL to show all triggers from a schema

Posted on 2007-11-20
7
1,258 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 35

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 35

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
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

738 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