Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1327
  • Last Modified:

SQL to show all triggers from a schema

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
HPvH
Asked:
HPvH
  • 2
  • 2
  • 2
  • +1
2 Solutions
 
joebednarzCommented:
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
 
johnsoneSenior Oracle DBACommented:
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
 
HPvHAuthor Commented:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Jinesh KamdarCommented:
You might not have access to DBA views.

Try this : -

SELECT * FROM user_triggers;
SELECT * FROM all_triggers WHERE owner = 'owner_schema';
0
 
Jinesh KamdarCommented:
The last SQL should have read : -

SELECT * FROM all_triggers WHERE owner = 'SALESMAN';
0
 
johnsoneSenior Oracle DBACommented:
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
 
HPvHAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now