Triggers on editioning views inheriting trigger events from NEWER editions [message #664990] |
Wed, 16 August 2017 08:21 |
|
fixxxer
Messages: 46 Registered: August 2014
|
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;
|
|
|
|
|
|
|
|
|
|