[SOLVED] ORA-22816 while updating Joined View with Instead of trigger

ORA-22816 while updating Joined View with Instead of trigger

I read a lot about it but didn't found any help on that.

My Situation: I've two database tables which belongs together. This tables I want to query with EntityFramework. Because Table B contains for EntityFramework the discriminator (for choosing the correct class for Table A) I've created a View which Joins Table A and Table B.

This join is quite simple. But: I want also to store data with that View. The issue is, that EntityFramework also wants to store the discriminator. But this isn't possible because it would update/insert into two tables.

So I've tried to create an "Instead of" trigger to just update/insert Table A (because Table B doesn't matter and will never be updated).

When I created the trigger - everything fine. If I insert something with an SQL Statement - everything is fine. But: If I'm inserting directly in the View (using Oracle SQL Developer) it throws the Exception as below:

ORA-22816 (unsupported feature with RETURNING clause).

If I do the same with EntityFramework I get the same error. Can someone help me?

Below my Code:

Table A and Table B:

CREATE Table "TableA" 
(
 "ID" Number NOT NULL,
 "OTHER_VALUESA" varchar2(255),
 "TableB_ID" number not null,
 CONSTRAINT PK_TableA PRIMARY KEY (ID)
);

CREATE Table "TableB" 
(
 "ID" Number NOT NULL,
 "NAME" varchar2(255),
 "DISCRIMINATOR" varchar2(255),
 CONSTRAINT PK_TableB PRIMARY KEY (ID)
);

The Joined View:

Create or Replace View "JoinTableAandB"
( 
  "ID",
  "OTHER_VALUESA",
  "TableB_ID",
  "DISCRIMINATOR"
) AS
select tableA.ID, tableA.OTHER_VALUESA, tableA.TableB_ID, tableB.DISCRIMINATOR
from TABLEA tableA
inner join TABLEB tableB on tableA.TableB_ID = tableB.ID;

And finally the Trigger:

create or replace TRIGGER "JoinTableAandB_TRG"
INSTEAD OF INSERT ON "JoinTableAandB"
FOR EACH ROW
BEGIN 
insert into TABLEA(OTHER_VALUESA, TABLEB_ID)
values (:NEW.OTHER_VALUESA, :NEW.TABLEB_ID);
END;

I've also tried it (to verify if the insert is correct just to enter "NULL" into the trigger instead of insert. But got the same error message.

Does anybody know how to solve this? Or does anybody have a good alternative (better Idea)?

Thanks!

Note: I've also defined a sequence for TableA's ID so that it will be generated automatically.

// Edit: I found a possible Solution for MS SQL:

https://stackoverflow.com/a/26897952/3598980

But I don't know how to translate this to Oracle... How can I return something from a trigger?

Solution

Note: I've also defined a sequence for TableA's ID so that it will be generated automatically.

In EF StoreGenerated keys in Oracle are incompatible with INSTEAD OF triggers. EF uses a RETURNING clause to output the store generated keys, which doesn't work with INSTEAD OF triggers.