Oralce: Why Do Triggers on Editioning Views in My Custom Schema Fail? According Tom Kyte on editioning views "you can put triggers on it, do to it things you would do to a table". However, my triggers on my editioning views in my custom schema don't fire. Tom doesn't get things wrong, so I must have something configured incorrectly. Any suggetions?

Create Table, Sequence and Trigger:

CREATE TABLE "MY_SCHEMA"."MY_TABLE" 
   ("TABLE_KEY" NUMBER(10,0) NOT NULL ENABLE, 
   "FIELD2" VARCHAR2(32) NOT NULL ENABLE, 
   "FIELD3" NUMBER NOT NULL ENABLE);

CREATE SEQUENCE  "MY_SCHEMA"."MY_SEQUENCE"  MINVALUE 1 
    MAXVALUE 1000000000000000000000000000 INCREMENT BY 1 
    START WITH 1 CACHE 20 NOORDER  NOCYCLE;

CREATE OR REPLACE TRIGGER "MY_TABLE_TRIGGER"
  BEFORE INSERT
  ON  "MY_SCHEMA"."MY_TABLE"       FOR EACH ROW
BEGIN
  SELECT MY_SEQUENCE.NEXTVAL INTO :NEW.table_key FROM dual;
END my_table_trigger;


Update Editioning:

exec ad_zd_table.upgrade('MY_SCHEMA', 'MY_TABLE');


That will create an editioned view that looks like this:

CREATE OR REPLACE FORCE EDITIONING VIEW "MY_SCHEMA"."MY_TABLE#" ("TABLE_KEY", "FIELD2", "FIELD3") AS 
  select TABLE_KEY TABLE_KEY, FIELD2 FIELD2, FIELD3 FIELD3 from "MY_SCHEMA"."MY_TABLE";


And it will modify the trigger so it looks like this:

CREATE OR REPLACE TRIGGER "MY_SCHEMA"."MY_TABLE_TRIGGER" 
  BEFORE INSERT

 ON  "MY_SCHEMA"."MY_TABLE#"        FOR EACH ROW
BEGIN
  SELECT MY_SEQUENCE.NEXTVAL INTO :NEW.table_key FROM dual;
END my_table_trigger;


Now we test:

INSERT INTO my_table
  (field2,
   field3) 
VALUES
  ('TEST', 1234)


Result:
    ORA-01400: cannot insert NULL into (“MY_SCHEMA”.MY_TABLE”.”TABLE_KEY”)
I think I figured it out. I believe this is by design. I tested this and it works.

INSERT INTO my_table#
  (field2,
   field3) 
VALUES
  ('TEST', 1234)


I believe the intention is that source code be modified to either directly reference the # view. Or reference a synonym that points to that view. I know, people are saying "reference an apps synonym" all over the web, but I didn't connect why until just now. Hope this is useful to the rest of you all.