SQL & PL/SQL

Dear everyone

I am trying to create a trigger instead of that updates multiple tables joined in a view but I cannot get my trigger to work. The create view command was as follows:

CREATE OR REPLACE VIEW VIEW_MI_JOIN_GC
AS
SELECT MAP_INDEX.mi_prinx,
       index_type_id,
       original_map_publication_id,
       original_map_sheet_number_id,
       name_of_feature,
       geog_coordinates_id,
       GEOG_COORDINATES.mi_prinx AS "mi_prinx_polygon",
       GEOG_COORDINATES.geographical_coordinates,
       GEOG_COORDINATES.mapinfo_style_row
 FROM MAP_INDEX
  JOIN GEOG_COORDINATES
   ON geog_coordinates_id=GEOG_COORDINATES.mi_prinx;

The above view links a polygon table to feature names table which means a polygon will appear more than once in a view, despite only one version of the polygon existing in the base table. This means direct updating of the view cannot occur, since 1 polygon could appear multiple times in a view. The original two base tables and their columns names are as follows:

MAP_INDEX
---------
MI_PRINX
INDEX_TYPE_ID
ORIGINAL_MAP_PUBLICATION_ID
ORIGINAL_MAP_SHEET_NUMBER_ID
NAME_OF_FEATURE
MAPINFO_STYLE_ROW
GEOGRAPHICAL_COORDINATES
GEOG_COORDINATES_ID

GEOG_COORDINATES
----------------
MI_PRINX
GEOGRAPHICAL_COORDINATES
MAPINFO_STYLE_ROW

The trigger I created was as follows:

CREATE OR REPLACE TRIGGER TRIG_VIEW_MI_JOIN_GC
   INSTEAD OF UPDATE ON VIEW_MI_JOIN_GC
      REFERENCING NEW AS NEW
 FOR EACH ROW
BEGIN
 UPDATE MAP_INDEX
  SET mi_prinx = :NEW.mi_prinx,
      index_type_id = :NEW.index_type_id,
      original_map_publication_id = :NEW.original_map_publication_id,
      original_map_sheet_number_id = :NEW.original_map_sheet_number_id,
      name_of_feature = :NEW.name_of_feature,
      mapinfo_style_row = :NEW.mapinfo_style_row,
      geographical_coordinates = :NEW.geographical_coordinates,
      geog_coordinates_id = :NEW.geog_coordinates_id
   WHERE geog_coordinates_id = :OLD.geog_coordinates_id;
 UPDATE GEOG_COORDINATES
  SET mi_prinx = :NEW.mi_prinx,
      geographical_coordinates = :NEW.geographical_coordinates,
      mapinfo_style_row = :NEW.mapinfo_style_row
   WHERE mi_prinx = :OLD.mi_prinx;
END;
/

The idea is that when I draw a new polygon in MapInfo and assign it a revised geog_coordinates_id number and mi_prinx_polygon number, which are the same, once I save the view it then updates the underlying tables. The geographical_coordinates and mapinfo_style_row columns of the map_index table are columns with older polygon data which although not currently having new data inserted into them, are required for the previous data they contain. This data is currently being added to the geog_coordinates table using other scripts. The idea is that all the data is then viewed using a view and updates made to the view, triggering the instead of trigger, so the data is not duplicated but still viewable as if it were.

When I first created the above trigger in SQLdeveloper it seems to run constantly. My computer then crashed, not related to this work, and I lost session because I had not committed it. I wasn't ready to commit it because I believe something is wrong.

Am I entering the trigger syntax correctly and am I going about this in the right way? I only want it to update the rows which have changed, which is why I have been using :NEW and :OLD.

Kind regards

Tim

Edited by: user467357 on Nov 18, 2008 6:07 PM
I amended my script slightly because there were a couple of errors. eg. begin, and old as old and view name typo