I have a dialog page in my Apex app that allows inserting into a View. The database view is defined something like this:
CREATE VIEW PLACE_PRIMARY_NAME
AS select
type
, capacity
, city
, state_country
, name
from place p
inner join place_names pn using (place_id);
I then have a trigger built:
CREATE OR REPLACE TRIGGER PLACE_PRIMARY_NAME_INS
INSTEAD OF INSERT ON PLACE_PRIMARY_NAME
for each row
declare
v_place_id place.place_id%TYPE;
BEGIN
-- insert into place first
insert into place(type,capacity,city,state_country)
values(:NEW.type,:NEW.capacity,:NEW.city,:NEW.state_country)
returning place_id INTO v_place_id;
-- insert the name
insert into place_names(place_id,name,primary)
values(v_place_id,:NEW.name,1);
END;
In my Apex page, I have a button to add a new place. It opens a dialog, where the user enters type, capacity, city, state_country and the primary name. I then want to return the created place_id back to Apex to set the value of a select list on a form. I've done this with other add buttons, but they were just adding to a single table, this button has to add to two tables, which is why I created the view. I know I could do some other logic - like returning the name, and then finding it in a SQL, but I'd rather return the exact place_id that was created. Is this possible when using a view like this? I'm using Apex 22.2.4 on OCI.
Thanks!