Edition Based Redefinition - Triggers on Editioning Views [message #659452] |
Wed, 18 January 2017 03:36 |
|
fixxxer
Messages: 45 Registered: August 2014
|
Member |
|
|
Hi all,
I have question regarding triggers on editioning views for a database that is utilizing Edition Based Redefinition. This is on: Oracle Database 11g Enterprise Edition 11.2.0.4.0 64 Bit Production, Linux.
We are seeing this problem in our production environment, but I have created a simple example (code below) to show the behavior. In short - when we add a column to the underlying table which the editioning view is based on, this does not invalidate the trigger on the editioning view - however, when we drop that new column it causes the trigger to go invalid, even though the editioning view does not make use of that column. Why would it go invalid when this column is not referenced at all in the editioniong view?
SQL> ALTER SESSION SET current_schema = sys;
Session altered.
SQL> ALTER USER bguiney ENABLE EDITIONS FORCE;
User altered.
SQL> CREATE EDITION test_edition AS CHILD OF ORA$BASE;
Edition created.
SQL> GRANT USE ON EDITION test_edition TO bguiney;
Grant succeeded.
SQL> ALTER SESSION SET current_schema = bguiney;
Session altered.
SQL> ALTER SESSION SET edition = test_edition;
Session altered.
SQL> CREATE TABLE test_drop_col(col1 VARCHAR2(10), col2 NUMBER);
Table created.
SQL> CREATE OR REPLACE EDITIONING VIEW test_drop_col_vw AS SELECT col1, col2 FROM test_drop_col;
View created.
SQL> CREATE OR REPLACE TRIGGER test_drop_col_trg
BEFORE INSERT OR UPDATE ON test_drop_col_vw
FOR EACH ROW
BEGIN
:NEW.col1 := 'TEST';
END;
/ 2 3 4 5 6 7
Trigger created.
SQL> SELECT object_name, status FROM dba_invalid_objects WHERE owner='BGUINEY';
no rows selected
SQL> ALTER TABLE test_drop_col ADD (col3 NUMBER);
Table altered.
SQL> SELECT object_name, status FROM dba_invalid_objects WHERE owner='BGUINEY';
no rows selected
SQL> ALTER TABLE test_drop_col DROP COLUMN col3;
Table altered.
SQL> SELECT object_name, status FROM dba_invalid_objects WHERE owner='BGUINEY';
OBJECT_NAME STATUS
-------------------------------------------------------------------------------------------------------------------------------- -------
TEST_DROP_COL_TRG INVALID
SQL>
|
|
|
|
|
|
|
|
|
|