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

Unlocking multiple triggers and diverse schemas

Ordering multiple triggers

Fancy a table with multiple triggers? Be Sherlock. Analyze the order of firing the triggers. Remember, they follow a sportsmanship spirit when they belong to the same timing/event combination (BEFORE UPDATE, for instance). Your ally here - trigger_order column from the ALL_TRIGGERS view.

Navigating through schema variations

• Peeping into another's schema: SELECT * FROM all_triggers WHERE table_owner = 'ANOTHER_SCHEMA'; Not spying, just browsing!
• Self-introspection: SELECT * FROM user_triggers; See the triggers answering to user you.

Super-efficient trigger management

Opt for these queries for peak performance in handling triggers:

• Status-check: Regular patrol to ensure triggers are enabled or disabled as per your wish.
• Event attendance: Stay aware of the triggers' presence at the triggering event (INSERT, UPDATE, DELETE, TRUNCATE). Find them using SELECT triggering_event FROM all_triggers;.
• Timing specs: SELECT trigger_type FROM all_triggers; precisely tells you the timing of the triggers (BEFORE, AFTER, INSTEAD OF).

Finding triggers sprawled across schemas

Unearth triggers around all schemas:

SELECT 
    OWNER || '.' || TRIGGER_NAME as FULL_TRIGGER_NAME, -- Schema ownership 
    TABLE_NAME
FROM 
    ALL_TRIGGERS;


Avail a full identifier that seamlessly blends the schema and trigger name.

References