Updateable view

Although this feature already exist a long time in Oracle, I’d like to share my experience using “updateable views” together with APEX.

My situation
I’ve a table “Teams”, a table “Team Groups” and a table “Team Team Groups” (which teams are in which groups - many/many relationship).

While I was inputting the Champions League teams I had to
1. input into table Teams
2. input into table Team Team Groups

To speed up the inserts in an APEX application, you wouldn’t create two insert pages (one per table), but only one page which handles the inserts for you. You can for ex. write some (APEX form) processing which will insert the data into the two tables or tell all items to which table they belong and adapt the standard processing.

I didn’t use any of these, I did the insert through a view… I built an APEX tabular form on top of the view and had my GUI very quick and easy!

An updateable view exists of a regular view and an “instead of” trigger…

My view
SELECT t.team_id, t.team_name, t.image_id, t.team_comment, t.is_active, g.team_group_id
FROM tdg_team t, tdg_team_team_group g
WHERE t.team_id = g.team_id;

My trigger
CREATE OR REPLACE TRIGGER tdg_vw_teams_groups_ioi_trg
INSTEAD OF INSERT ON tdg_vw_teams_groups
DECLARE
BEGIN
INSERT INTO tdg_team
(team_id, team_name, image_id, team_comment, is_active)
VALUES (:NEW.team_id, :NEW.team_name, :NEW.image_id, :NEW.team_comment, :NEW.is_active);
INSERT INTO tdg_team_team_group
(team_id, team_group_id)
VALUES (:NEW.team_id, :NEW.team_group_id);
END tdg_vw_teams_groups_ioi_trg;

Almost the same for Update and Delete…

To see if the view is really updateable, run this statement
SELECT column_name, updatable, insertable, deletable
FROM user_updatable_columns
WHERE table_name = ‘TDG_VW_TEAMS_GROUPS’;

My insert statement
INSERT INTO tdg_vw_teams_groups
(team_id, team_name, team_comment, image_id, is_active, team_group_id)
VALUES (tdg_team_seq.NEXTVAL, ‘Shakhtar’, NULL, NULL, 1, 1011);

I found it very useful to speed up my inserts like this and this solution also works from SQL Plus, SQL Developer, etc.