Oracle: SQL query to find all the triggers belonging to the tables?

Question

how can i find all the triggers that belong to a table?

yanjost · Accepted Answer · 2014-08-12 03:38:45Z

130

The following will work independent of your database privileges:

select * from all_triggers
where table_name = 'YOUR_TABLE'

The following alternate options may or may not work depending on your assigned database privileges:

select * from DBA_TRIGGERS

or

select * from USER_TRIGGERS

edited Aug 12, 2014 at 3:38

user84118

answered Feb 4, 2011 at 9:56

yanjost

5,5212 gold badges28 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

gnuchu Over a year ago

you may want to add "where table-name = 'FOOBAR'" to that query to narrow your answers down.

Arpit Jain Over a year ago

Note: table_name should be in uppercase.

Wilfred Hughes · Accepted Answer · 2018-05-29 15:15:45Z

6

Another table that is useful is:

SELECT * FROM user_objects WHERE object_type='TRIGGER';

You can also use this to query views, indexes etc etc

edited May 29, 2018 at 15:15

Wilfred Hughes

31.5k15 gold badges149 silver badges202 bronze badges

answered Feb 4, 2011 at 10:10

diagonalbatman

18.1k3 gold badges33 silver badges31 bronze badges

3 Comments

user330315 Over a year ago

That should be object_type = 'TRIGGER' as Oracle is case-sensitive, but ALL_TRIGGERS is better as it also show to which table the trigger belongs (which is not available in ALL_OBJECTS)

diagonalbatman Over a year ago

Indeed, I have edited. I was just trying to highlight that the user_objects table was handy for other things.

shasi kanth Over a year ago

You may want to further narrow down your search, by adding: and object_name like '%foo%';

user330315 · Accepted Answer · 2011-02-04 09:54:29Z

5

Check out ALL_TRIGGERS:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_2107.htm#i1592586

answered Feb 4, 2011 at 9:54

user330315

Comments

tbone · Accepted Answer · 2022-02-18 12:09:52Z

-6

Use the Oracle documentation and search for keyword "trigger" in your browser.

This approach should work with other metadata type questions.

edited Feb 18, 2022 at 12:09

answered Feb 14, 2011 at 14:04

tbone

15.5k3 gold badges37 silver badges42 bronze badges

Collectives™ on Stack Overflow

Oracle: SQL query to find all the triggers belonging to the tables?

4 Answers 4

2 Comments

3 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

3 Comments

Comments

Comments

Linked

Related