Untitled

WITH TriggerDetails AS (
    SELECT 
        t.owner, 
        t.trigger_name, 
        t.table_name, 
        t.triggering_event, 
        t.status, 
        o.LAST_DDL_TIME AS created_time
    FROM all_triggers t
    JOIN all_objects o 
        ON t.owner = o.owner AND t.trigger_name = o.object_name
    WHERE t.trigger_name LIKE 'TRG_%'
      AND o.owner = 'RMS'
      AND o.LAST_DDL_TIME > TIMESTAMP '2024-12-23 14:39:00'
)
SELECT 
    td.owner, 
    td.trigger_name, 
    td.table_name, 
    td.triggering_event, 
    td.status, 
    td.created_time,
    fk.constraint_name AS foreign_key_name,
    fk.table_name AS fk_table_name,
    fk_col.column_name AS fk_column_name,
    pk.table_name AS referenced_table_name,
    pk_col.column_name AS referenced_column_name
FROM TriggerDetails td
LEFT JOIN all_constraints fk 
    ON fk.owner = td.owner 
   AND fk.table_name = td.table_name 
   AND fk.constraint_type = 'R'
LEFT JOIN all_cons_columns fk_col 
    ON fk.owner = fk_col.owner 
   AND fk.constraint_name = fk_col.constraint_name
LEFT JOIN all_constraints pk 
    ON fk.r_constraint_name = pk.constraint_name 
   AND fk.r_owner = pk.owner
LEFT JOIN all_cons_columns pk_col 
    ON pk.owner = pk_col.owner 
   AND pk.constraint_name = pk_col.constraint_name
   AND fk_col.position = pk_col.position
ORDER BY td.created_time DESC, td.trigger_name, fk.constraint_name;