1

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”)

2
  • 5
    Edit your question with a full example of what is not working. We have nothing to troubleshoot & guessing is not constructive
    – Philᵀᴹ
    Dec 8, 2016 at 23:27
  • Thanks Philᵀᴹ, walking through creating the example gave me the answer!
    – Jeff
    Dec 9, 2016 at 14:54

1 Answer 1

0

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.