Pre-19c Crossedition Triggers

As shown in the previous post, finding all the crossedition triggers in the database is easy using DBA_TRIGGERS_AE, but this can be done only as of Oracle 19c – the release in which this data dictionary view was added.

Here are two options that can be used before 19c.

A Proper Way

We can use DBA_OBJECTS_AE to find all the triggers across all editions, and then for each one to “jump” to its edition using dbms_sql and check whether it is a crosseedition trigger or not using DBA_TRIGGERS.

declare
    l_cur          number;
    l_rows         number;
    l_status       dba_triggers.status%type;
    l_crossedition dba_triggers.crossedition%type;
begin
    for o in (select owner,
                     edition_name,
                     object_name
              from   dba_objects_ae
              where  object_type = 'TRIGGER'
              and    edition_name is not null
              order  by owner,
                        edition_name,
                        object_name)
    loop
        l_cur := dbms_sql.open_cursor();
        dbms_sql.parse(
            c             => l_cur,
            statement     => 'select status,crossedition 
                              from   dba_triggers
                              where  owner = :owner
                              and    trigger_name = :trigger_name
                              and    crossedition != ''NO''',
            language_flag => dbms_sql.native,
            edition       => o.edition_name);
        dbms_sql.bind_variable(l_cur, ':owner', o.owner);
        dbms_sql.bind_variable(l_cur, ':trigger_name', o.object_name);
        dbms_sql.define_column(l_cur, 1, l_status, 8);
        dbms_sql.define_column(l_cur, 2, l_crossedition, 7);
        l_rows := dbms_sql.execute_and_fetch(l_cur);
        if l_rows > 0 then
            dbms_sql.column_value(l_cur, 1, l_status);
            dbms_sql.column_value(l_cur, 2, l_crossedition);
            dbms_sql.close_cursor(l_cur);
            dbms_output.put_line(
                utl_lms.format_message(
                    '%s.%s is a %s crossedition trigger defined in edition %s, and it is %s',
                    o.owner,
                    o.object_name,
                    l_crossedition,
                    o.edition_name,
                    l_status));
        end if;
    end loop;
end;
/

An Undocumented Way

Looking at the definition of DBA_TRIGGERS, it seems that when a trigger is a crossedition one, the 14^th bit of the trigger$.property column is set.
In addition, if the 18^th bit is set, the crossedition trigger is a Reverse one, and if not, it’s a Forward one.
If these assumptions are correct, we can write the following query:

select o.owner,
       o.object_name trigger_name,
       o.edition_name,
       decode(t.enabled, 1, 'ENABLED', 'DISABLED') status,
       decode(bitand(t.property, 131072), 131072, 'REVERSE', 'FORWARD') crossedition
from   sys.trigger$   t,
       dba_objects_ae o
where  bitand(t.property, 8192) = 8192
and    o.object_id = t.obj#
order  by owner,
          edition_name,
          trigger_name;