One of the fundamental building blocks of EBR (Edition Based Redefinition) is Crossedition Triggers.
This special type of triggers is used when we change the structure of tables in an online application upgrade, and the data of these tables may be changed while we do the upgrade and afterwards, until no session uses the pre-upgrade edition. These triggers keep the pre-upgrade representation of the data and post-upgrade representation of the data in sync.
Crossedition triggers are temporary in nature – they should be dropped when all the clients use the edition in which the triggers were created (or one of its descendent editions).
In theory, crossedition triggers live for a very short period.
In real life, when we have multiple active editions and different types of clients that may keep using older editions for a while before upgrading to the latest edition, it may take days and even weeks before we can drop the crossedition triggers. In my opinion, this is perfectly fine, and actually one of the benefits that I see in EBR.
Another significant difference between crossedition triggers and regular triggers is that crossedition triggers are not inherited by descendent editions. However, they may be triggered by DML operations that are executed in a descendent edition.
This means that we need a way to check which crossedition triggers exist in our schema/database.
Before Oracle 19c we did not have a simple way to do it.
We could find all the crossedition triggers in the current edition:
select *
from user_triggers
where crossedition != 'NO';
But there may be other crossedition triggers in ancestor editions that affect us, and they are not returned by this query.
And we could find all the triggers across all the editions:
select *
from user_objects_ae
where object_type = 'TRIGGER';
But we can’t tell from this query which of these triggers are crossedition ones.
In Oracle 19c a new data dictionary view – USER_TRIGGERS_AE – was added, to describe all the triggers, across all editions, that are owned by the current user.
The corresponding DBA_TRIGGERS_AE and ALL_TRIGGERS_AE were added as well, of course.
It seems that I’ve had some influence on the introduction of these views 🙂
So now, in order to find all the crossedition triggers in my schema, I can simply use this query:
select t.*
from user_triggers_ae t
where t.crossedition != 'NO';
In the next post I’ll show two ways to achieve the same results before 19c.