• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1322
  • 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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