Searching for Triggers in the Database

Create this simple function and use it to search for triggers.

 create or replace    function trigger_like
     ( p_schema in varchar2,
       p_trigger_name in varchar2,
       p_search in varchar2
     )
     return number
     as
             l_text long;
    begin
            select trigger_body
              into l_text
              from all_triggers
             where owner = p_schema
               and trigger_name = p_trigger_name;
            if ( upper(l_text) like upper(p_search) )
            then
                    return 1;
            else
                    return 0;
            end if;
    exception
            when no_data_found then return null;
    end;
Once created use this sql to search for triggers:


select trigger_name
      from all_triggers
     where trigger_like( owner, trigger_name, '%X%' ) = 1