Hi All:
First, here is the reference discussion and youtube (presented by Insum) related to this topic:
Discussion: How to use Edition-Based Redefinition within Apex — oracle-tech
Youtube: (3) Demystifying Edition Based Redefinition - YouTube
if you want youtube on minute 56, Richard is explain "Pure Data Triggers should stay in base table". I totally agree with guidance.
here is my question:
Environment: EBS R12.2.10
APEX 19.x (actually version of apex is irrelevant for this question).
my apex custom/parsing schema called xx_apex.
in xx_apex, I am having a table kevin_test_ebr and a trigger on it defined as below:
<code>
CREATE TABLE xx_apex.KEVIN_TEST_EBR (
ID NUMBER NOT NULL
CONSTRAINT KEVIN_TEST_EBR_PK
PRIMARY KEY,
row_version_number NUMBER,
notification_name VARCHAR2(255) NOT NULL,
notification_description VARCHAR2(4000) NULL,
created_by VARCHAR2(255) NOT NULL,
created DATE NOT NULL,
updated_by VARCHAR2(255) NOT NULL,
UPDATED DATE NOT NULL
)
/
CREATE OR REPLACE TRIGGER xx_apex.KEVIN_TEST_EBR_BIU
BEFORE INSERT OR UPDATE ON REPOSITORYAPEX.KEVIN_TEST_EBR
FOR EACH ROW
BEGIN
IF INSERTING AND :NEW.ID IS NULL THEN
SELECT TO_NUMBER(SYS_GUID(),'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')
INTO :NEW.ID
FROM dual;
END IF;
IF INSERTING THEN
:NEW.created_by := COALESCE(SYS_CONTEXT('APEX$SESSION','APP_USER'),USER);
:NEW.created := SYSDATE;
:NEW.updated_by := COALESCE(SYS_CONTEXT('APEX$SESSION','APP_USER'),USER);
:NEW.UPDATED := SYSDATE;
:NEW.row_version_number := 1;
END IF;
IF UPDATING THEN
:NEW.row_version_number := NVL(:OLD.row_version_number,1) + 1;
:NEW.updated_by := COALESCE(SYS_CONTEXT('APEX$SESSION','APP_USER'),USER);
:NEW.UPDATED := SYSDATE;
END;
</code>
As you can see the trigger in this case is for audit track and maintain id (primary key), this fit perfectly with what Richard mentioned in the youtube.
Per Oracle EBS R12.2.x upgrade, we need run below as APPS to upgrade this table:
BEGIN
APPS.AD_ZD_TABLE.UPGRADE(
x_table_owner => 'REPOSITORYAPEX',
x_table_name => 'KEVIN_TEST_EBR'
);
END;
/
however, with this approach Oracle (above az_zd_table.upgrade()) procedure will create edition view (KEVIN_TEST_EBR#) for my table and drop trigger from base table and move the trigger to edition view as below:
Here is my question (hope Richard and Martel can guide):
what is the command/instruction I should do in EBS R12.2.10 for my custom table so that after upgrade "pure data trigger" is continually with my base table instead of being moved to edition view?
Thanks!
Kevin