Home » SQL & PL/SQL » SQL & PL/SQL » Triggers on editioning views inheriting trigger events from NEWER editions (Oracle Database - Enterprise Edition Product Version 12.1.0.2 Linux x86-64 OS Version Red Hat Enterprise 6)
Triggers on editioning views inheriting trigger events from NEWER editions [message #664990] Wed, 16 August 2017 08:21 Go to next message
fixxxer
Messages: 20
Registered: August 2014
Junior Member
We use Oracle Edition Based Redefinition. We have editioning views for all of our tables. Some of the tables require a trigger, so we have created the triggers on the editioning views (not CROSS EDITION TRIGGERS).

We are seeing a problem in the below scenario:

1) "editioning_view" has an "AFTER INSERT" trigger against it, on EDITION1.
2) We create EDITION2 as a child of EDITION1.
3) In EDITION2, we change the "AFTER INSERT" trigger on "editioning_view" to be an "AFTER INSERT OR UPDATE" trigger. Deploy these changes to the database.
4) The application pointing to the database is still operating on EDITION1, and executes an update on "editioning_view", which results in the trigger being fired, and using the trigger from EDITION1.

What is happening in point 4 is wrong - it is using the triggering event from EDITION2 (the AFTER UPDATE component of the trigger), but using the trigger body from EDITION1.

Point 4 should be using the trigger events and trigger body from EDITION1, since the application is connecting with EDITION1.

Example code for simulating this situation, should be run for a user with DBA role and EDITIONS ENABLED:

--Set session details
ALTER SESSION SET CURRENT_SCHEMA = fixxxer;
ALTER SESSION SET EDITION = ora$base;


--Create table with Primary Key
CREATE TABLE ebr_triggers_1_tbl (value VARCHAR2(10));
ALTER TABLE ebr_triggers_1_tbl ADD CONSTRAINT ebr_triggers_1_pk PRIMARY KEY (value);


--Create editioning view for the table, which will have an AFTER INSERT trigger on it
CREATE OR REPLACE EDITIONING VIEW ebr_triggers_1_ev
AS
SELECT value FROM ebr_triggers_1_tbl;



--Create second table with Primary Key
CREATE TABLE ebr_triggers_2_tbl (value VARCHAR2(10),
                                 event VARCHAR2(10));
ALTER TABLE ebr_triggers_2_tbl ADD CONSTRAINT ebr_triggers_2_pk PRIMARY KEY (value);


--Create editioning view for the second table
CREATE OR REPLACE EDITIONING VIEW ebr_triggers_2_ev
AS
SELECT value,event FROM ebr_triggers_2_tbl;


--Create trigger on the first table, which will insert values into the second table, AFTER INSERT on table 1
--Trigger event is prefixed with "1"
CREATE OR REPLACE TRIGGER ebr_triggers_1_trg
AFTER INSERT ON ebr_triggers_1_ev
FOR EACH ROW
DECLARE
  v_event   ebr_triggers_2_ev.event%TYPE;
BEGIN

  IF INSERTING THEN
     v_event := '1INSERTING';
  ELSE
     v_event := '1OTHER';
  END IF;

  INSERT INTO ebr_triggers_2_ev (value,event) VALUES (:NEW.value, v_event);

END ebr_triggers_1_trg;
/


--Test the trigger by insert 2 rows into the 1st editioning view
INSERT INTO ebr_triggers_1_ev(value) VALUES ('TEST');
INSERT INTO ebr_triggers_1_ev(value) VALUES ('TEST1');

COMMIT;

SELECT * FROM ebr_triggers_1_ev;
SELECT * FROM ebr_triggers_2_ev;



--Create new edition, which is a child of ORA$BASE
CREATE EDITION orabase2 as child of ora$base;
GRANT USE ON EDITION orabase2 TO public;


--Change to the new edition, and update the trigger to be AFTER INSERT OR UPDATE
ALTER SESSION SET EDITION = orabase2;
ALTER SESSION SET CURRENT_SCHEMA = fixxxer;


--Notice the trigger now has the event prefixed with "2"
CREATE OR REPLACE TRIGGER ebr_triggers_1_trg
AFTER INSERT OR UPDATE ON ebr_triggers_1_ev
FOR EACH ROW
DECLARE
  v_event   ebr_triggers_2_ev.event%TYPE;
BEGIN

  IF INSERTING THEN
     v_event := '2INSERTING';
  ELSE
     v_event := '2OTHER';
  END IF;

  INSERT INTO ebr_triggers_2_ev (value,event) VALUES (:NEW.value, v_event);

END ebr_triggers_1_trg;
/

--Switch back to the base edition, where the trigger is only AFTER INSERT
ALTER SESSION SET EDITION = ora$base;

--UPDATE 1 row in the 1st editioning view, to change the value
UPDATE ebr_triggers_1_ev SET value = 'TEST99' WHERE value = 'TEST';
COMMIT;

--Editioning view 1 has only 2 rows, but editioning view 2 has 3 rows now, and the event is showing as "1OTHER". 
--This shows the trigger does not see the event as INSERTING, it sees it is UPDATING, but still fires the trigger.
--Also, the event is prefixed with "1", which shows it is using the code from ora$base, but seems to be obeying the conditions of orabase2 (since it is firing on UPDATE)

SELECT * FROM ebr_triggers_1_ev;
SELECT * FROM ebr_triggers_2_ev;
Re: Triggers on editioning views inheriting trigger events from NEWER editions [message #664991 is a reply to message #664990] Wed, 16 August 2017 08:29 Go to previous messageGo to next message
BlackSwan
Messages: 25633
Registered: January 2009
Location: SoCal
Senior Member
submit Bug Report to MOS
Re: Triggers on editioning views inheriting trigger events from NEWER editions [message #664992 is a reply to message #664991] Wed, 16 August 2017 10:18 Go to previous messageGo to next message
fixxxer
Messages: 20
Registered: August 2014
Junior Member
BlackSwan wrote on Wed, 16 August 2017 14:29
submit Bug Report to MOS
I raised an SR - just wanted to check on here if anyone experienced this, or seen something I was doing wrong Smile
Re: Triggers on editioning views inheriting trigger events from NEWER editions [message #665026 is a reply to message #664992] Fri, 18 August 2017 04:18 Go to previous messageGo to next message
Michel Cadot
Messages: 65166
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Don't see anything wrong, reproduced in 11.2.0.4.21, 12.1.0.2.160719 and 12.2.0.1.170718.

Re: Triggers on editioning views inheriting trigger events from NEWER editions [message #665027 is a reply to message #665026] Fri, 18 August 2017 04:27 Go to previous messageGo to next message
fixxxer
Messages: 20
Registered: August 2014
Junior Member
Michel Cadot wrote on Fri, 18 August 2017 10:18

Don't see anything wrong, reproduced in 11.2.0.4.21, 12.1.0.2.160719 and 12.2.0.1.170718.

When you say don't see anything wrong, you mean this problem isn't happening for you, or it is happening?
Re: Triggers on editioning views inheriting trigger events from NEWER editions [message #665028 is a reply to message #665027] Fri, 18 August 2017 05:29 Go to previous messageGo to next message
Michel Cadot
Messages: 65166
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I meant I don't see anything wrong in what you did and your interpretation, I saw the same problem in all these versions.

Just a thought: why there are no DBA_TRIGGERS_AE views whereas you have it for dba_objects/source/views/editioning_views/errors...?

Re: Triggers on editioning views inheriting trigger events from NEWER editions [message #665029 is a reply to message #665028] Fri, 18 August 2017 05:35 Go to previous messageGo to next message
fixxxer
Messages: 20
Registered: August 2014
Junior Member
Michel Cadot wrote on Fri, 18 August 2017 11:29

I meant I don't see anything wrong in what you did and your interpretation, I saw the same problem in all these versions.

Just a thought: why there are no DBA_TRIGGERS_AE views whereas you have it for dba_objects/source/views/editioning_views/errors...?


ahh, thanks! Not sure - definitely found a few snags along the way with Edition Based Redefinition; although, it is a fantastic feature.
Re: Triggers on editioning views inheriting trigger events from NEWER editions [message #665030 is a reply to message #665029] Fri, 18 August 2017 05:36 Go to previous message
Michel Cadot
Messages: 65166
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
although, it is a fantastic feature.
I fully concur.

Previous Topic: Help with PL SQL query - Analytical Function or group by
Next Topic: Determine filePath/Name from UTL_FILE.FILE_TYPE?
Goto Forum:
  


Current Time: Tue Sep 19 10:30:01 CDT 2017

Total time taken to generate the page: 0.13493 seconds