CODEWITHSUNDEEP
sqloracle-database
SteveC81
SteveC81

Reputation: 35

ORA 04088 Trigger Issue

I have a trigger that I would like to populate the geometry column after an update or a new record is created. The trigger I have built is as follows:

CREATE OR REPLACE TRIGGER SFS_TRIGGER 
AFTER INSERT OR UPDATE ON SPORTS_FACILITIES_STRATEGY 
BEGIN
  UPDATE sports_facilities_strategy
     SET GEOMETRY = MDSYS.SDO_GEOMETRY(2001,81989, MDSYS.SDO_POINT_TYPE(EASTING,
                                                                        NORTHING,
                                                                        NULL),
                                       NULL,NULL);

END;

however I keep getting the following error message:

ORA-04088: error during execution of trigger 'GIS_ADMIN.SFS_TRIGGER'
ORA-06512: at "GIS_ADMIN.SFS_TRIGGER", line 2

I get that it is struggling around the time of the update but I'm stuck! Any help/suggestions greatly appreciated.

Upvotes: 0

Views: 6949

Answers (2)

Alex Poole
Alex Poole

Reputation: 191235

The update in the trigger will re-fire the same trigger, so it will loop forever - the error is Oracle killing the loop.

To just set the geometry field on the row that is causing the trigger to fire, you don't issue an update, you use the :new syntax to set the value. I think you want a before trigger though...

CREATE OR REPLACE TRIGGER SFS_TRIGGER 
BEFORE INSERT OR UPDATE ON SPORTS_FACILITIES_STRATEGY 
FOR EACH ROW
BEGIN
  :NEW.GEOMETRY := MDSYS.SDO_GEOMETRY(2001,81989,
    MDSYS.SDO_POINT_TYPE(:NEW.EASTING, :NEW.NORTHING, NULL), NULL,NULL);
END;
/

... assuming NORTHING and EASTING are columns in the SPORTS_FACILITIES_STRATEGY table)

Which is similar to this example in the documentation.

Upvotes: 3

Amit Arora
Amit Arora

Reputation: 175

Oracle ORA-06512 was thrown because the you may be trying to insert a row in the trigger which had to fire before insert. The trigger was causing itself to fire over and over and get stuck in a loop.

Upvotes: 0

Related Questions