I've got two tables, corresponding to
Nodes in a graph and
Aisles to which they may belong. In addition, each
Aisle has a
Node at which it starts and ends.
Aisle char(4) Start_Node int not null End_Node int not null ...
This table would have foreign keys referencing the
Node table (deleting on cascade), but this is apparently not possible (multiple cascade paths?) so I have to enforce this rule myself in a trigger on the
Node Table...which looks like so:
Node_ID int x float y float Aisle char(4) [MAY BE NULL]
Now, the trigger on the node table does some data integrity stuff, and I'm pretty sure a trigger is the right place to do it since I absolutely cannot think of a situation where I wouldn't want the trigger to fire -- but it doesn't always fire...
If I manually delete an entry from the
Aisle table to which rows in
Node reference, the deletion cascades to the
Node table and the trigger definitely fires, as expected.
But if I delete a record from the
Node table to which
Aisle rows reference, the trigger on the Node table enforces the pseudo-foreign key constraint in
Aisle (deleting rows in
Aisle by design), which should then cascade the deletions back to
Node and call the trigger again (by design), but I have verified multiple times that the trigger never gets called after the cascade occurs.
What's going on here? I'm clearly missing some detail on how triggers and FKs interact...for the record, nested triggers are active, I'm using an After Delete trigger, and I'm definitely no DBA.