CODEWITHSUNDEEP
oracle-databasedatabase-designtriggers
Richard
Richard

Reputation: 3100

Foreign Keys or Delete Trigger?

The tables: 

SIGN_OBJECT:
ID VARCHAR2(32) PRIMARY KEY, 
DESCRIPTION VARCHAR2(100),
X NUMBER(10,3), 
Y NUMBER(10,3),
...
GEOMETRYID VARCHAR2(32) 

LAMPPOST_OBJECT:
ID VARCHAR2(32) PRIMARY KEY, 
DESCRIPTION VARCHAR2(100),
X NUMBER(10,3), 
Y NUMBER(10,3),
...
GEOMETRYID VARCHAR2(32) 

OBJGEOMETRY:
GEOMETRYID VARCHAR2(32) PRIMARY KEY, 
GEOMETRY MDSYS.SDO_GEOMETRY, 
...

There are many X_OBJECT tables. Unfortunately the schema designers (in their infinite wisdom) didn't see any crossover between the various object types. I am unable to change these tables without creating much more work.

For each object table there is a trigger that creates the relevant SDO_GEOMETRY value BEFORE insert or update ( GEOMETRYID is unique - it comes from a sequence ). At the moment the trigger calls a package function which inserts the OBJGEOMETRY record and returns the geometryid.

The problem is that if the parent record is deleted I would like the OBJGEOMETRY child record to be also deleted.

Initially I thought this could be done with Foreign Keys cascade delete, but of course the FK requires a Primary Key in the parent table - obviously this won't work.

However, I discovered that actually a FK requires a unique constraint in the parent table. I can make X_OBJECT.GEOMETRYID unique but then I'm finding issues because that GEOMETRYID isn't yet populated in the parent table but the FK requires it exists. I cannot do that inside the trigger ( by setting :NEW.GEOMETRYID ) so do I have to write the GEOMETRYID first and then commit? I'm not sure and this has bad code smell.

So am I wrong? Is this a more suitable case for a delete trigger? or is there something I'm missing.

Thanks.

Upvotes: 1

Views: 200

Answers (2)

wolφi
wolφi

Reputation: 8361

The triggers should fire before insert or update, not after. Then you can set :NEW.GEOMETRYID with the value returned by your package.

Besides, the foreign keys point the wrong way. It should be ALTER TABLE x_OBJECT ADD FOREIGN KEY (geometryid) REFERENCES objgeometry(geometryid);

Therefore, you'll need a delete trigger...

Upvotes: 0

user330315
user330315

Reputation:

If you insert both the OBJGEOMETRY and the X_OBJECT rows in the same transaction, then you can set the FK to DEFERRABLE INITIALLY DEFERRED.

In that case it will be evaluated at COMMIT time, not when you run the INSERT statement.

Upvotes: 1

Related Questions